calculating median and mode

[russelmiller]

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?

 

[russelmiller]

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

 

[daddylonglegs]

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.php?t=623157&highlight=median

 

[Max]

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

 

[russelmiller]

This worked beautifully, thanks max!

 

[Max]

welcome, glad to hear that.