Hi Epinn

The last response from Excellent, is using a different separator to
you(and I).
If you change the formula to
=SUM((A1:E1)*{1,2,3,4,5})/SUM(A1:E1)
then you will get the answer 2.789474
the same as the non-array entered
=SUMPRODUCT((A1:E1)*{1,2,3,4,5})/SUM(A1:E1)

If this method is to be used, then I would suggest wrapping in a
ROUND( ,0)

Since the OP said they had results of
A1:E1: 3 5 6 3 2
relating to
1,1,1,2,2,2,2,2,3,3,3,4,4,4,4,4,4,5,5
the median value is 3 as there are 9 numbers above it, and 9 below.

However, if you enter this data in A1:S1 on another sheet and do
Median(A1:S1) you get 3 as the result.
Now delete columns FGH and the resulting formula changes to
Median(A1:P1) and the result is correctly 3.5.

Go back to your first sheet and change B1 to 2 to reflect the fact that
you have deleted 3 2's from the sequence and the Median calculation
remains at 3, and the Sum/Sumproduct calculations change to 2.9375

I am no statistician, but I would conclude that there is no substitute
to carrying out the median calculation on the full set of data, and that
other methods using a smaller number of cells representing the frequency
of numbers is but an approximation, which could be flawed dependant upon
the dataset.
Jerry Lewis of Harlan would be likely to give a definitive answer on
this.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi,

I assume you are *not* answering my question but the OP's? My question
referred to the specific data set of A1:E5 discussed earlier and the
median function =median(A1:E5). I know we need CSE (array) when we use
MEDIAN(IF( etc. I was interested in why Philippe said we must have CSE
for =median(A1:E5).

The following link says CSE not necessary.

"Referencing more than a single row or column will also give the correct
median value. Entering these formulas using (Ctrl + Shift + Enter) is
not necessary......"

Source: http://www.bettersolutions.com/excel...I647548581.htm

On to your formula ......

A1:E1: 3 5 6 3 2 as OP stated.

If I am not mistaken your formula (with CSE) returns 15 which is not the
median. Am I missing something?

Please explain. My purpose is to learn. Thank you.

Epinn

"excelent" wrote in message
...
{=SUM((A1:E1)*{1;2;3;4;5})/SUM(A1:E1)}



"Epinn" skrev:

Hi Philippe,

...... but =MEDIAN(A1:E6) wouldn't make sense unless you had an
array ...... <<

Can you elaborate this statement, please? Can you give me an example
that using CSE (Ctrl+Shift+Enter) will give the correct result whereas
not using it will give the wrong result?

Using the data set suggested by you

A B C D E

1 1 1 2 2
2 2 2 3 3
3 4 4 4 4
4 4 5 5

and many other data sets that I created, I always came up with the
same correct result without using CSE. I even used data sets that had
values not in a sorted (ascending) sequence; no problem whatsoever.

I really want to know what you meant by way of an example. Thanks.

Epinn

"Philippe L. Balmanno" wrote in message
...
"Bruce" wrote in message
...
Greetings all: All I want to do is calculate the median of grouped
data.
I
have five cols. of data corresponding to a 1:5 Likert scale. For
example:
3
ones, 5 twos, 3 threes, 6 fours, 2 fives. I have 19 values, so the
10th
ranked value would be the median. The 10th value resides in the
"threes"
column, so that's my median value. But how to do this via a
function?

Do I expand these cells like this?:
=MEDIAN(1,1,1,2,2,2,2,2,3,3,3,4,4,4,4,4,4,5,5), which I don't know
how to
do,
by the way, or is there a simpler way?

Many thanks in advance...
--
Bruce

Well if you have your values in a row columns A1:S1 then
if this was placed in T1: =MEDIAN(A1:S1) It would bring up your
Median. If
your numbers were in a column like in A1:A19 then =MEDIAN(A1:A19) but
=MEDIAN(A1:E6) wouldn't make sense unless you had an array.