Interesting Median observation

[Bony Pony]

Am I going crazy?

Create 16 values so that the formula
=median(a1:a16)

does not return the value of an actual cell you have typed.

Now use
=median(a1:a16,1)

and it gives you the cell in your range that is closest (lower range) to the
median!

Can anyone explain this?

Regards,
Bony

 

[Mike H]

Hi,

It isn't doing that. Consider the numbers below In a1 to a17

=MEDIAN(A1:A16) Returns 8.5

=MEDIAN(A1:A16,22) Returns 9

=MEDIAN(A1:A17) Returns 9

All your doing by adding ,1 to the median arguments is adding an additional
number for it to calculate.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
22

Mike

 

[MartinW]

Hi,

The help file explains that clearly. In your first formula you
have an even number in your set.

With your second formula read it in the help file as
Number1 (is your A1:A16 range)
Number2 (is 1)

HTH
Martin

 

[Bony Pony]

Hi all,
Thanks for the steer. Glad I'm not going crazy - just a localised case of
dumbness!!

many thanks!

Bony

 

[MartinW]

Your welcome Bony, and don't worry about the dumbness bit, I don't
think there is one user in this group that doesn't spin off on the wrong
tangent each now and again, and that includes our resident gurus!!!

Cheers
Martin

 

[MartinW]

Speaking of dumbness, that should have been You're welcome Bony.

 

[T. Valko]

To put it another way:

If the total number of values is an odd number, then the median value will
be one of the values being evaluated. If the the total number of values is
an even number, then the median will be the average of the middle two values
and will only be one of the values being evaluated if the two middle numbers
are the same.

 

[Bony Pony]

Many thanks for the help and explanations.

To put it another way:

If the total number of values is an odd number, then the median value will
be one of the values being evaluated. If the the total number of values is
an even number, then the median will be the average of the middle two values
and will only be one of the values being evaluated if the two middle numbers
are the same.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Many thanks for the help and explanations.