Medianif with 2 conditions

[bowriter]

Hi folks,

I'm trying to find the median of a column using two conditions.

I would like to calculate the median of column M, if column G cells
are less than 1995 but not when "0" (zero) has been entered in column
M (">0"). I've tried all sorts of combinations but none work so
far.

The following formula works well but doesn't take into consideration
the "0" condition:

{=MEDIAN(IF($G$4:$G$200>1995, $M$4:$M$200))}

Any help would be appreciated,

Robert

 

[Glenn]

Hi folks,

I'm trying to find the median of a column using two conditions.

I would like to calculate the median of column M, if column G cells
are less than 1995 but not when "0" (zero) has been entered in column
M (">0"). I've tried all sorts of combinations but none work so
far.

The following formula works well but doesn't take into consideration
the "0" condition:

{=MEDIAN(IF($G$4:$G$200>1995, $M$4:$M$200))}

Any help would be appreciated,

Robert

{=MEDIAN(IF(G$4:$G$200>1995,IF($M$4:$M$200<>0,$M$4:$M$200)))}

 

[Luke M]

The trick is to multiple your truth arrays, creating a single true/false
array output. Input this array* formula:

=MEDIAN(IF((G4:G200<1995)*(M4:M200>0),M4:M200))

*Use Ctrl+Shift+Enter to confirm formula, not just enter.

 

[Glenn]

{=MEDIAN(IF(G$4:$G$200>1995,IF($M$4:$M$200<>0,$M$4:$M$200)))}

Actually, I did that as "M is not equal to 0". Drop the < if you meant "M is
greater than 0".

 

[Underbooks]

Actually, I did that as "M is not equal to 0".  Drop the < if you meant"M is
greater than 0".

Hi Folks,

I should have been more specific and indeed, your formula does "work",
but I wanted to clarify:

Column M contains values from zero to various positive integers. I
need the median of those values *except* when the corresponding cell
in Column G is less 1995 *and* when the cell in Column M is 0 (zero).

That might change the formula?

~R

 

[Underbooks]

Hi Folks,

I should have been more specific and indeed, your formula does "work",
but I wanted to clarify:

Column M contains values from zero to various positive integers. I
need the median of those values *except* when the corresponding cell
in Column G is less 1995 *and* when the cell in Column M is 0 (zero).

That might change the formula?

~R

So:
If 1994 (column g) and 0 (it's corresponding cell in column M) = is
not calculated into the median
But:
If 1997 and and 0 = is calculated into the median

 

[Luke M]

Ah, thanks for clarification. Rewritten formula (still an array)

=MEDIAN(IF((G4:G200<1995)*(M4:M200=0),"x",M4:M200))

Formula now spits out an "x" for values that are to be ignored in the MEDIAN
function.

 

[Glenn]

So:
If 1994 (column g) and 0 (it's corresponding cell in column M) = is
not calculated into the median
But:
If 1997 and and 0 = is calculated into the median

Clear as mud now...

Which statement (if any) is correct:

1. Include rows where G>1995 or M=0
2. Include rows where G>1995 or M<>0
3. Include rows where G>1995 and M=0
4. Include rows where G>1995 and M<>0

 

[bowriter]

Ah, thanks for clarification. Rewritten formula (still an array)

=MEDIAN(IF((G4:G200<1995)*(M4:M200=0),"x",M4:M200))

Formula now spits out an "x" for values that are to be ignored in the MEDIAN
function.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*






- Show quoted text -

Luke:

That's perfect!! Works like a charm (I would've never come up with
that "x" vaules thing): thank you again and sorry it was a bit
confusing.

Thanks all,

Robert