Find Median of Positive numbers only in Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column containing both positive and negative numbers
I need a formula to find the Median of all positive numbers, and a separate
formula to find the Median of all negative numbers
Any help gratefully acknowledged
 
MichaelC said:
I have a column containing both positive and negative numbers
I need a formula to find the Median of all positive numbers, and a separate
formula to find the Median of all negative numbers
Any help gratefully acknowledged
Click to expand...

The following are array formulas entered with cntrl+shft+enter

=MEDIAN(IF(B2:B13>0,B2:B13))
=MEDIAN(IF(B2:B13<0,B2:B13))

Peter atherton
 
I have a column containing both positive and negative numbers
I need a formula to find the Median of all positive numbers, and a separate
formula to find the Median of all negative numbers
Any help gratefully acknowledged
Click to expand...


Will there be 0's?

With no 0's, then the **array** formula:

=MEDIAN(IF(rng>0,rng))

for positive numbers and

=MEDIAN(IF(rng<0,rng))

for negative numbers.

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

If 0's will be included as positive numbers, then something like:

=MEDIAN(IF((rng>=0)*(rng<>""),rng))

(also array-entered) should do the trick.


--ron
 
Thank you very much Ron and Peter.

Ron Rosenfeld said:
Will there be 0's?

With no 0's, then the **array** formula:

=MEDIAN(IF(rng>0,rng))

for positive numbers and

=MEDIAN(IF(rng<0,rng))

for negative numbers.

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

If 0's will be included as positive numbers, then something like:

=MEDIAN(IF((rng>=0)*(rng<>""),rng))

(also array-entered) should do the trick.


--ron
Click to expand...
 
Back
Top