Jun 15 2010, 02:48 PM
Ok, so I'm trying to do a calculation that is conceptually pretty easy: calculate medians, month by month.
Here is my formula:
=IF(Z2:Z950>3994, MEDIAN(IF(Z2:Z950<40026, AF2:AF950)))
Column Z is a bunch of dates. Column AF is a bunch of integers. And I use CSE.
In the first instance in which I use this formula, it works and outputs the correct value. However, when I move on to the next month, like so:
=IF(Z2:Z950>40025, MEDIAN(IF(Z2:Z950<40057, AF2:AF950))), it returns the value of FALSE.
I can't figure this out. Why does it output that value? It's supposed to output a number. What does this mean? I've checked the cell properties and everything seems to be in order. Can anyone help shed some light on this? I'd appreciate it.
Jun 15 2010, 04:32 PM
The way I see it right now... as I click along various sections in your formula... up on the formula bar... the syntax aid shows that Excel thinks that the second "if" statement is the first number in the MEDIAN function. That sure doesn't seem right to me. So far, I haven't been able to find the syntax solution.
Jun 17 2010, 10:41 AM
This is sometimes returning false as this is the default response to the data failing at the first IF test. Try changing your formula to:
=MEDIAN(IF(Z2:Z950>3994, IF(Z2:Z950<40026, AF2:AF950,""),""))
And remember to CSE.
Jun 17 2010, 12:20 PM
Jun 18 2010, 01:12 AM
CSE = Use Control+Shift+Enter to commit the formula. This makes it work as an array function.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here