MEDIAN

[gronkette1]

I need to compute the MEDIAN value for the following list, but I must ignore the blanks and FALSE (it skews the result).

thank you !


$28.95
$28.00
$-
$28.44
$27.40
$27.92
$28.44
$27.92
$-
$-
$26.88
$-
$27.92
$-
$27.50
FALSE
$-
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE

 

[Audiojoe]

I tried =MEDIAN(A1:A10) where A1 to A10 contains the values I want to compute. I included FALSE and blanks and it worked fine

 

[gronkette1]

When you do it that way you get $27.45

The true value is $27.92 (by excluding the blanks and 0).

 

[Aladin Akyurek]

On 2002-04-11 08:05, gronkette1 wrote:
I need to compute the MEDIAN value for the following list, but I must ignore the blanks and FALSE (it skews the result).

thank you !


$28.95
$28.00
$-
$28.44
$27.40
$27.92
$28.44
$27.92
$-
$-
$26.88
$-
$27.92
$-
$27.50
FALSE
$-
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE

Array-enter:

=MEDIAN(IF(A1:A24,A1:A24))

where A1:A24 houses the target data.

In order to array-enter a formula, you need to hit control+shift+enter, no just enter.

I edited the array-formula, because it appears that you want to exclude the 0 values.

Aladin
This message was edited by Aladin Akyurek on 2002-04-11 08:25

 

[gronkette1]

I still get $27.45.... the true value should be $27.92...

 

[Jay Petrulis]

On 2002-04-11 09:50, gronkette1 wrote:
I still get $27.45.... the true value should be $27.92...

With Aladin's formula non array-enetered I get 27.45.

With it array-entered, I get #VALUE! though it seems it should work.

When I array-entered
=MEDIAN(IF(A1:A24<>0,A1:A24))

the formula returned 27.92 for me.

Please make sure you are correctly entering this as an array formula.

Bye,
Jay

 

[Yogi Anand]

On 2002-04-11 09:50, gronkette1 wrote:
I still get $27.45.... the true value should be $27.92...

Hi gronkette1:
Your data set may be mixed up ... I multiplied your data set by 1 and then took the median and I did get
=MEDIAN(A1:A24) to give 27.92

 

[Jay Petrulis]

On 2002-04-11 10:17, Yogi Anand wrote:

On 2002-04-11 09:50, gronkette1 wrote:
I still get $27.45.... the true value should be $27.92...

Hi gronkette1:
Your data set may be mixed up ... I multiplied your data set by 1 and then took the median and I did get
=MEDIAN(A1:A24) to give 27.92

Hi Yogi,

Did you copy the data from the post? If so, you have the $- (blanks in the OP's terms) being treated as text, which is ignored with the MEDIAN function. That is why the problem is the zero values and not the FALSE cells.

Change the $- to zero and you should get 27.45.

Regards,
Jay

P.S. I did the exact same thing as I suspect you did.

 

[gronkette1]

You guys are the greatest!!!

I used {=MEDIAN(IFL4:L41<>0,L4:L41))}

and it returned $27.92

thank you, thank you!

 

[Yogi Anand]

On 2002-04-11 10:35, gronkette1 wrote:
You guys are the greatest!!!

I used {=MEDIAN(IFL4:L41<>0,L4:L41))}

and it returned $27.92

thank you, thank you!

Hi gronkette1:
Just a minor editorial and substantative:

editorial: the formula actually is
{=MEDIAN(IF(L4:L41<>0,L4:L41))}

substantative: you don't actually need <>0
{=MEDIAN(IF(L4:L41,L4:L41))}



Hi