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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert

bowriter wrote:
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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert


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

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:M2000),M4:M200))

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

--
Best Regards,

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


"bowriter" wrote:

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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert

Glenn wrote:
bowriter wrote:
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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert


{=MEDIAN(IF(G$4:$G$2001995,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".

On Oct 6, 11:49*am, Glenn wrote:
Glenn wrote:
bowriter wrote:
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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert

{=MEDIAN(IF(G$4:$G$2001995,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".

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

On Oct 6, 12:22*pm, Underbooks wrote:
On Oct 6, 11:49*am, Glenn wrote:



Glenn wrote:
bowriter wrote:
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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert

{=MEDIAN(IF(G$4:$G$2001995,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".

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

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!*


"Underbooks" wrote:

On Oct 6, 12:22 pm, Underbooks wrote:
On Oct 6, 11:49 am, Glenn wrote:



Glenn wrote:
bowriter wrote:
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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert

{=MEDIAN(IF(G$4:$G$2001995,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".

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

Underbooks wrote:
On Oct 6, 12:22 pm, Underbooks wrote:
On Oct 6, 11:49 am, Glenn wrote:



Glenn wrote:
bowriter wrote:
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$2001995, $M$4:$M$200))}
Any help would be appreciated,
Robert
{=MEDIAN(IF(G$4:$G$2001995,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".
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


Clear as mud now...

Which statement (if any) is correct:

1. Include rows where G1995 or M=0
2. Include rows where G1995 or M<0
3. Include rows where G1995 and M=0
4. Include rows where G1995 and M<0

On Oct 6, 1:10*pm, Luke M wrote:
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!*



"Underbooks" wrote:
On Oct 6, 12:22 pm, Underbooks wrote:
On Oct 6, 11:49 am, Glenn wrote:

Glenn wrote:
bowriter wrote:
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$2001995, $M$4:$M$200))}

Any help would be appreciated,

Robert

{=MEDIAN(IF(G$4:$G$2001995,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".

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- Hide quoted text -

- 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