Just a coment to my formula : {=SUM((A1:E1)*{1;2;3;4;5})/SUM(A1:E1)}
in Denmark ve use semicol ; instead of using comma , as they do in USA
so I forgot to change ; to , - my mistake
{ } means Array-formula - is inserted when u use CTRL+SHIFT+ENTER


"Epinn" skrev:

Thank you Roger. I am a dummy on statistics so I don't attempt to get to the bottom. Just want to comment on a few things.

I didn't see the semicolons in the formula until you pointed out. I saw commas. <G
Yes, I did feel that I could use SUMPRODUCT instead. I have seen quite a few SUMPRODUCT examples before but this is the first time I find out that semicolon is acceptable and it does the calculation differently. (I use evaluate formula to analyse.)

As far as I am concerned, the formula with commas is a weighted average formula and I am always under the impression that weighted average and median are two different animals.

Now delete columns FGH and the resulting formula changes to
Median(A1:P1) and the result is correctly 3.5. <<

...... and the Median calculation remains at 3...... <<

I got 3 and I am sure 3.5 was a typo. Let's not worry about it.

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. <<

I totally agree. I am surprised that it returned the correct result even for a small data set. As I said earlier, I regarded the SUM/SUMPRODUCT formula as weighted average calculation. 3, 5, 6, 3, 2 are the quantities of different products and 1, 2, 3, 4, 5 are the unit price for each product respectively. The formula returns the average unit price of 19 items.

I'll let this go. However, if you care to comment on SUMPRODUCT and semicolon, I would be grateful.

I am going to start my own thread "SUMPRODUCT - comma versus semicolon"

Epinn

"Roger Govier" wrote in message ...
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.