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

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

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

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!

In article ,
Barb Reinhardt wrote:

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

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

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?

"Domenic" wrote:

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!

In article ,
Barb Reinhardt wrote:

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

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

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

"Domenic" wrote:

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!

In article ,
Barb Reinhardt wrote:

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

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

In article ,
Barb Reinhardt wrote:

This does what I want, thanks.

You're welcome! Glad I could help!

How do I copy it over a bunch of cells?

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?

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!

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

Try...

=MEDIAN(IF(D$2:D$107=D111,IF(F$2:Q$107<"",F$2:Q$1 07)))

Hope this helps!

In article ,
Barb Reinhardt wrote:

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

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

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

"Domenic" wrote:

Try...

=MEDIAN(IF(D$2:D$107=D111,IF(F$2:Q$107<"",F$2:Q$1 07)))

Hope this helps!

In article ,
Barb Reinhardt wrote:

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