Full Version: Strange result....
imants
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.

Thanks!
briangriffey
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.
StuKiel
Hi,

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.

HTH
Stu
briangriffey
CSE???
StuKiel
Hi,

CSE = Use Control+Shift+Enter to commit the formula. This makes it work as an array function.

HTH
Stu