MEDIAN, STDEV of a range IF another range = specific value

B

Barb Reinhardt

I want to get the MEDIAN of the values in F2:Q107 only if the value in
D2:D107 = D111, how do I do this. I want to do the same thing with MEDIAN.
I don't want to use a pivot table if I can avoid it.

Thanks in advance,
Barb Reinhardt
 
D

Domenic

Try the following formulas, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=MEDIAN(IF(D2:D107=D111,F2:Q107))

and

=STDEV(IF(D2:D107=D111,F2:Q107))

Hope this helps!
 
B

Barb Reinhardt

This does what I want, thanks. How do I copy it over a bunch of cells? DO
I have to confirm each and every one?
 
B

Barb Reinhardt

I have cells in F2:Q107 that are blank and the calculations appear to treat
those as zeros. What needs to be changed.
 
D

Domenic

Barb Reinhardt said:
This does what I want, thanks.
Click to expand...

You're welcome! Glad I could help!
How do I copy it over a bunch of cells?
Click to expand...

If you're copying the formula down the column...

=MEDIAN(IF(D$2:D$107=D111,F$2:Q$107))

If you're copying the formula across the row...

=MEDIAN(IF($D2:$D107=D111,$F2:$Q107))
DO I have to confirm each and every one?
Click to expand...

No, you only need to confirm with CONTROL+SHIFT+ENTER for the first
cell, then you can copy or drag the formula down or across...

Hope this helps!
 
B

Barb Reinhardt

Now why didn't I think of that. I tried using IF(AND()) and it didn't like
what I had.
 
Top