calculating median and mode
 

I have a column of the actual data selection, it the adjacent column i have
the frequencies of each of the data collected. How do I calculate median and
mode from that data? can i calculate this directly from the frequencies, or
is there some way i can create a separate column with a whole list of the
data entries using the frequency?

calculating median and mode
 

or a better way to put it:
I have the frequency of my data, is there anyway to take the frequency and
create columns of the actual data points?
Take this
Data Frequency
1 5
2 2
3 3
4 4
5 3

and make it
1
1
1
1
1
2
2
..
..
..
or even
1 2 3 4 5
1 2 3 4 5
1 3 4 5
1 4
1

calculating median and mode
 

Hello russell,

Does the data column have duplicates? If not then the modal value is simply
the one that corresponds to the max frequency in that column, i.e.

=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

For the median try this link:

http://www.excelforum.com/showthread...ghlight=median

"russelmiller" wrote:

or a better way to put it:
I have the frequency of my data, is there anyway to take the frequency and
create columns of the actual data points?
Take this
Data Frequency
1 5
2 2
3 3
4 4
5 3

and make it
1
1
1
1
1
2
2
.
.
.
or even
1 2 3 4 5
1 2 3 4 5
1 3 4 5
1 4
1

calculating median and mode
 

One formulas way to re-arrange it as per your option2, viz:

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

Source data assumed in cols A and B, data from row2 down

Put in D1:
=IF(INDEX($A:$A,COLUMNS($A:A)+1)=0,"",INDEX($A:$A, COLUMNS($A:A)+1))

Copy D1 across as far* as required to exhaust the data in col A, eg to H1.
Any extra cols in the copy across will just return neat looking blanks: "".
*subject to the max cols per sheet, of course

Then put in D2:
=IF(D$1="","",IF(ROWS($1:1)(INDEX($B:$B,MATCH(D$1 ,$A:$A,0))-1),"",D$1))

Copy D2 across to H2, fill down as far as required to cover at least the max
frequency - 1** (less one line) for any data in col B. Eg if the max
frequency in col B is 10 (say), then just copy down by at least: 10-1= 9
lines. Any extra lines in the copy down will simply return neat looking
blanks: "".
**The formula in D1 across will return the 1st occurence
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"russelmiller" wrote:
or a better way to put it:
I have the frequency of my data, is there anyway to take the frequency and
create columns of the actual data points?
Take this
Data Frequency
1 5
2 2
3 3
4 4
5 3

and make it
1
1
1
1
1
2
2
.
.
.
or even
1 2 3 4 5
1 2 3 4 5
1 3 4 5
1 4
1

calculating median and mode
 

This worked beautifully, thanks max!

calculating median and mode
 

welcome, glad to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"russelmiller" wrote in message
...
This worked beautifully, thanks max!