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
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" wrote:
> 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
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
On Thu, 23 Jun 2005 17:23:11 -0700, "MichaelC" <[email protected]>
wrote:
>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
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" wrote:
> On Thu, 23 Jun 2005 17:23:11 -0700, "MichaelC" <[email protected]>
> wrote:
>
> >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
>
>
> 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
>
On Thu, 23 Jun 2005 18:26:01 -0700, "MichaelC" <[email protected]>
wrote:
>Thank you very much Ron and Peter.
You're welcome. Glad to help.
--ron
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks