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

=MEDIAN(A1:E6)


"Bruce" skrev:

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

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

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

5 columns (6) 4's
so your data would look like this {=MEDIAN(A1:E4)}
A B C D E

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

Hi PHilippe: Thanks for responding. My data currently looks like this:

A B C D E
3 5 6 3 2

where (A, B, C, D, E) correspond to the number of responses I get for
ratings of (1, 2, 3, 4, 5), respectively. For example, a "6" in column C
indicates that I have 6 responses of a rating "3".

The median for this "grouped" data is 3 as there are 8 responses below the 3
rating and 8 above. But doing an "=MEDIAN(A1:E1) would give me the median
value of the group (2,3,3,5,6), when what I actually want is the that of the
groups(1,1,2,2,2,3,3,3,4,4,4,4,4,5,5,5,5,5,5). Does this make sense? But
how to do it???

-Bruce
--
Bruce


"Philippe L. Balmanno" wrote:

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

5 columns (6) 4's
so your data would look like this {=MEDIAN(A1:E4)}
A B C D E

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

"Bruce" wrote in message
...
Hi PHilippe: Thanks for responding. My data currently looks like this:

A B C D E
3 5 6 3 2

where (A, B, C, D, E) correspond to the number of responses I get for
ratings of (1, 2, 3, 4, 5), respectively. For example, a "6" in column C
indicates that I have 6 responses of a rating "3".

The median for this "grouped" data is 3 as there are 8 responses below the
3
rating and 8 above. But doing an "=MEDIAN(A1:E1) would give me the median
value of the group (2,3,3,5,6), when what I actually want is the that of
the
groups(1,1,2,2,2,3,3,3,4,4,4,4,4,5,5,5,5,5,5). Does this make sense? But
how to do it???

-Bruce
--
Bruce


"Philippe L. Balmanno" wrote:

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

5 columns (6) 4's
so your data would look like this {=MEDIAN(A1:E4)}
A B C D E

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

Because each column corresponds to a value, what you want then is the max
value in the row
A B C D E
1 3 5 6 3 2
=MAX(A1:E1) would return 6 where Column C corresponds to the rating of 3 and
there are 6

"Philippe L. Balmanno" wrote in message
...
"Bruce" wrote in message
...
Hi PHilippe: Thanks for responding. My data currently looks like this:

A B C D E
3 5 6 3 2

where (A, B, C, D, E) correspond to the number of responses I get for
ratings of (1, 2, 3, 4, 5), respectively. For example, a "6" in column C
indicates that I have 6 responses of a rating "3".

The median for this "grouped" data is 3 as there are 8 responses below
the 3
rating and 8 above. But doing an "=MEDIAN(A1:E1) would give me the
median
value of the group (2,3,3,5,6), when what I actually want is the that of
the
groups(1,1,2,2,2,3,3,3,4,4,4,4,4,5,5,5,5,5,5). Does this make sense?
But
how to do it???

-Bruce
--
Bruce


"Philippe L. Balmanno" wrote:

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

5 columns (6) 4's
so your data would look like this {=MEDIAN(A1:E4)}
A B C D E

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

Because each column corresponds to a value, what you want then is the max
value in the row
A B C D E
1 3 5 6 3 2
=MAX(A1:E1) would return 6 where Column C corresponds to the rating of 3
and there are 6

You should consider the way you are recording your data. If the entire data
set has a chance of multiple equal ratings, I wouldn't compress it as much
as you have. The reason is what if for some fluke you come up with:
A B C D E
1 3 6 6 3 2
where would your mode be? At the rating of 2 or 3? If this possibility
isn't an issue then forget I mentioned it. If it is then you're better off
disigning a Frequency Distribution table and using Tools/Data
Analysis/Descriptive Statistics like this: (noter the mode and the max and
you will see why I chose =MAX(A1:E1)

Rating Frequency
1 3
2 5
3 6
4 3
5 2

Rating Frequency

Mean 3 Mean 3.8
Standard Error 0.707106781 Standard Error 0.734846923
Median 3 Median 3
Mode #N/A Mode 3
Standard Deviation 1.58113883 Standard Deviation 1.643167673
Sample Variance 2.5 Sample Variance 2.7
Kurtosis -1.2 Kurtosis -1.687242798
Skewness 0 Skewness 0.518420528
Range 4 Range 4
Minimum 1 Minimum 2
Maximum 5 Maximum 6
Sum 15 Sum 19
Count 5 Count 5

Assuming that your counts are in A1:A5, that the corresponding values are
1,...,5, and that no count exceeds 6, the array formula
=MEDIAN(IF(ROW(A1:E6)<=A1:E1,COLUMN(A1:E1)))
should give what you want.

Note that array formulas must be array entered (Ctrl-Shift-Enter)

Jerry

"Bruce" wrote:

Hi PHilippe: Thanks for responding. My data currently looks like this:

A B C D E
3 5 6 3 2

where (A, B, C, D, E) correspond to the number of responses I get for
ratings of (1, 2, 3, 4, 5), respectively. For example, a "6" in column C
indicates that I have 6 responses of a rating "3".

The median for this "grouped" data is 3 as there are 8 responses below the 3
rating and 8 above. But doing an "=MEDIAN(A1:E1) would give me the median
value of the group (2,3,3,5,6), when what I actually want is the that of the
groups(1,1,2,2,2,3,3,3,4,4,4,4,4,5,5,5,5,5,5). Does this make sense? But
how to do it???

-Bruce
--
Bruce


"Philippe L. Balmanno" wrote:

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

5 columns (6) 4's
so your data would look like this {=MEDIAN(A1:E4)}
A B C D E

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

More generally, the following array formula assumes only that the counts and
values are each given in a row and that there is no negative or non-numeric
data in these ranges

=MEDIAN(IF((ROW(OFFSET(counts,0,0,MAX(counts),COLU MNS(counts)))-ROW(counts)+1)<=counts,values))

Jerry

"Jerry W. Lewis" wrote:

Assuming that your counts are in A1:A5, that the corresponding values are
1,...,5, and that no count exceeds 6, the array formula
=MEDIAN(IF(ROW(A1:E6)<=A1:E1,COLUMN(A1:E1)))
should give what you want.

Note that array formulas must be array entered (Ctrl-Shift-Enter)

Jerry

"Bruce" wrote:

Hi PHilippe: Thanks for responding. My data currently looks like this:

A B C D E
3 5 6 3 2

where (A, B, C, D, E) correspond to the number of responses I get for
ratings of (1, 2, 3, 4, 5), respectively. For example, a "6" in column C
indicates that I have 6 responses of a rating "3".

The median for this "grouped" data is 3 as there are 8 responses below the 3
rating and 8 above. But doing an "=MEDIAN(A1:E1) would give me the median
value of the group (2,3,3,5,6), when what I actually want is the that of the
groups(1,1,2,2,2,3,3,3,4,4,4,4,4,5,5,5,5,5,5). Does this make sense? But
how to do it???

-Bruce
--
Bruce


"Philippe L. Balmanno" wrote:

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

5 columns (6) 4's
so your data would look like this {=MEDIAN(A1:E4)}
A B C D E

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

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.

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

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.

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.

Note that this formula uses grouped data to calculate the arithmetic mean,
not the median.

Jerry

"excelent" wrote:

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

Hi Bruce,

To calculate the median of grouped data, you can use the following steps:

1. Create a table with two columns: one for the Likert scale values and one for the frequency of each value. In your example, it would look like this:
   
   | Likert Scale | Frequency |
   |--------------|-----------|
   | 1 | 3 |
   | 2 | 5 |
   | 3 | 3 |
   | 4 | 6 |
   | 5 | 2 |
   
2. Add a third column for the cumulative frequency. This column will show the running total of the frequency column. In your example, it would look like this:
   
   | Likert Scale | Frequency | Cumulative Frequency |
   |--------------|-----------|----------------------|
   | 1 | 3 | 3 |
   | 2 | 5 | 8 |
   | 3 | 3 | 11 |
   | 4 | 6 | 17 |
   | 5 | 2 | 19 |
   
3. Calculate the median position using the formula (n+1)/2, where n is the total number of values. In your example, n is 19, so the median position is (19+1)/2 = 10.
   
4. Find the median value by looking up the cumulative frequency that corresponds to the median position. In your example, the median position is in the range of the "threes" column, which has a cumulative frequency of 11. Therefore, the median value is 3.
   

You can use the following formula to calculate the median value directly from the grouped data:

This is an array formula, so you need to press Ctrl+Shift+Enter instead of just Enter to enter it. The formula assumes that your table starts in cell A1 and that the Likert scale values are in column A and the frequencies are in column B.