Excel 2007 - can Median(IF)) be used with multi criteris - Median(if(and()))?

[aaghassi]

I need to find the median of data based on several pieces of criteria.
I have found that Median can be use with IF as an array formula, but i can't find anything to say that I can use several pieces of critera from various columns on the IF

Can I use Median(if(and(...,....,...,))) as an array formula in Excel 2007?

thanks

 

[Marcelo Branco]

Welcome to Mr Excel

To create an AND condition in an array formula you should multiply the conditions.

=MEDIAN(IF((range1=criteria1)*(range2=criteria2)*(range3=criteria3),somerange))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Aladin Akyurek]

AND's can be mapped onto IFs...

=MEDIAN(IF(X=x,IF(Y>y,Z)))

which says Feed the values of Z to median when the values of X equals x and the values of Y are greater than y. The upper case letters stand for equally sized multicell ranges and the lower case letters for criteria that must hold.

Note that such formulas array-processing formulas, so they need confirming with control+shift+enter, not just enter.

 

[aaghassi]

Thank you Aladin and Marcelo!
Multiplying the conditions did not work quite right for my situation. Some of my data results was a zero (0) and sometimes it needed to be considered in the median and sometimes it did not. Multiplying conditions did not seem to let me limit the zeros to only the circumstances that were valid to include.

Before i received these responses, i stumbled upon another post that was similar and took pieces of it that ended up being this same nested IF idea.
The nested IF does seem to allow me to distinguish when i will consider the zero in my median and when i will not.
My Excel results are not matching someone else's SAS calculated results - so i will now try to make sure all of my conditions match theirs. Wish me luck!!!

Thank you both!!

Ann

 

[Aladin Akyurek]

Thank you Aladin and Marcelo!
Multiplying the conditions did not work quite right for my situation. Some of my data results was a zero (0) and sometimes it needed to be considered in the median and sometimes it did not. Multiplying conditions did not seem to let me limit the zeros to only the circumstances that were valid to include.

Before i received these responses, i stumbled upon another post that was similar and took pieces of it that ended up being this same nested IF idea.
The nested IF does seem to allow me to distinguish when i will consider the zero in my median and when i will not.
My Excel results are not matching someone else's SAS calculated results - so i will now try to make sure all of my conditions match theirs. Wish me luck!!!

Thank you both!!

Ann

You are welcome. Unless Excel and SAS differ in how they implement MEDIAN, they should not differ qua results.

 

[sarahsarahsarah]

You are welcome. Unless Excel and SAS differ in how they implement MEDIAN, they should not differ qua results.

Hi all,

I have looked through messages on the forum on Median and If functions and I cant seem to get my formula right... any help would be appreciated.

=MEDIAN(IF((K2:K66,">=1")*(BM2:BM66,"yes"),K2:K66)

Thanks in advance

 

[Aladin Akyurek]

Hi all,

I have looked through messages on the forum on Median and If functions and I cant seem to get my formula right... any help would be appreciated.

=MEDIAN(IF((K2:K66,">=1")*(BM2:BM66,"yes"),K2:K66)

Thanks in advance

Control+shift+enter, not just enter:

=MEDIAN(IF(K2:K66 > 1, IF(BM2:BM66 = "yes", K2:K66)))

 

[sarahsarahsarah]

Thanks Aladin!!!- This has helped me finally work out how to use IF functions... it seems the IF needs to be put in front of every new condition and then the range is out at the end. Cheer!!

 

[Aladin Akyurek]

Thanks Aladin!!!- This has helped me finally work out how to use IF functions... it seems the IF needs to be put in front of every new condition and then the range is out at the end. Cheer!!

You are welcome. Yes regarding IF. It does effect filtering based on a condition.