Medianif Array Question

[ystatnik]

Hi guys,


I would greatly appreciate your help with an array formula I am writing. I'm a complete novice so excuse me if this is really trivial.

The logic: Median of "birth age", if in the "birth unit" range the birth unit equals to "one of the units in this range".

"Birth Age" is found in E:E,
"Birth Unit Range" is G:G.
The defined unit range is H2:H30.

Thanks very much!


Best


E

 

[barry houdini]

Hello ystatnik, welcome to MrExcel, try this formula

=MEDIAN(IF(ISNUMBER(MATCH(G2:G100,H2:H30,0)),E2:E100))

Which needs to be confirmed with CTRL+SHIFT+ENTER so that curly bracee like { and } appear around the formula in the formula bar.

Note: I used a specific range like E2:E100 rather than the whole column E:E. You can only use the whole column if you have Excel 2007

 

[ystatnik]

Hey Barry,


Thanks very much!!! I have been fiddling around with the array to apply to my data, however, it seems to be doing two things that I can't quite control:
(1)When I apply the formula to the entire data the formula starts counting beyond H30, where of course the rows are blank. I can manually fix that but don't know the formula fix.
(2)The formula is calculating the same median for all units, perhaps the designation of looking at the entire age spectrum of E2:E100 is the problem, the formula doesnt seem to actually instruct to only look in the E column rows where there is a match in G and H?

Thanks again!!

 

[barry houdini]

I think I probably misunderstood what you wanted to do.

Do you want a formula to put in I2 copied down to I30 that gives a median for each unit in H2:H30? If so then try this formula in I2

=MEDIAN(IF(G$2:G$100=H2,E$2:E$100))

confirmed with CTRL+SHIFT+ENTER and copied down to I30

 

[ystatnik]

Mate, your brilliant, that did the trick!

 

[boris1]

Hi, I was wondering if someone could help me with a similar formula. It's been stumping me for days.

I'm looking for a median of values in column D with a range determined by whether or not column N is a positive number.

Column D has integers and blank cells from while column N contains a formula with outputs such as:
0.0099
(0.1930)
(1.0000)
#NUM!
#DIV/0!
0.0120

So in this case, I would only like the two cells in column D that correspond to the 0.0099 and 0.0120 values in column N included in the range.

Thanks for any help.

Boris

 

[barry houdini]

Try an array formula like this

=MEDIAN(IF(ISNUMBER(N2:N100),IF(N2:N100>0,IF(D2:D100<>"",D2:D100))))

confirmed with CTRL+SHIFT+ENTER

 

[boris1]

Thank you Barry, that worked great. You are amazing.

My original idea of:
=MEDIAN(IF(N2:N100>=0,D2:D100))

was foiled by the #NUM!s and #DIV/0!s.

Aladin's formula
=MEDIAN(IF(ISNUMBER(N2:N10),IF(N2:N10>0,D2:D10)))
worked too.