Invert a frequency table?

[Chuck W.]

Hi,

Can anyone tell me how to "invert" a frequency table? That is, I have
some data organized into two columns: "Frequency Count" and "Number"
like so:

4 1
3 2
2 3

and I'd like to convert it to a single column like this:

1
1
1
1
2
2
2
3
3

The reason I'm trying to do this is because I would like to use a
function such as STDEV.S(), but I need the "expanded" version of the
data...

Thanks very much for any help you can provide.

 

[Salmon Egg]

Hi,

Can anyone tell me how to "invert" a frequency table? That is, I have
some data organized into two columns: "Frequency Count" and "Number"
like so:

4 1
3 2
2 3

and I'd like to convert it to a single column like this:

1
1
1
1
2
2
2
3
3

The reason I'm trying to do this is because I would like to use a
function such as STDEV.S(), but I need the "expanded" version of the
data...

Thanks very much for any help you can provide.

The obvious and simple way is to use a macro. You can, however use a
spreadsheet to do you own calculation.

Start with the data in two column as presented by you initially.

The sum of the numbers in A is the number of data.

Make two more columns C and D.

In a C cell, enter the product of A and B for that row.

Add items in C and divide by Sum A. That will be the mean of the
distribution.

Make a column D.

In each cell of D, take the square of the difference between the data
value B and the mean, and multiply by the weighting given in A.

Use the sum of the variances in C in your favorite standard deviation
formula with the number of samples given by Sum A.

Bill

 

[Rick Rothstein]

Can anyone tell me how to "invert" a frequency table? That is, I have

some data organized into two columns: "Frequency Count" and "Number"
like so:

4 1
3 2
2 3

and I'd like to convert it to a single column like this:

1
1
1
1
2
2
2
3
3

Here is a macro that will do what you asked for...

Sub ExpandTable()
Dim X As Long, LastRow As Long
Const StartRow As Long = 5
Const StartCol As Long = 3
LastRow = Cells(Rows.Count, StartCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow Step -1
Rows(X + 1).Resize(Cells(X, StartCol).Value - 1).Insert
Cells(X, StartCol).Resize(Cells(X, StartCol).Value). _
Value = Cells(X, StartCol + 1).Value
Next
Columns(StartCol + 1).Clear
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

*** Correction ***

Sorry, I left some test values in the constant (Const) statements without
mention them. I'm assuming your table starts in A1, so the code should have
looked like this....

Sub ExpandTable()
Dim X As Long, LastRow As Long
Const StartRow As Long = 1
Const StartCol As Long = 1
LastRow = Cells(Rows.Count, StartCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow Step -1
Rows(X + 1).Resize(Cells(X, StartCol).Value - 1).Insert
Cells(X, StartCol).Resize(Cells(X, StartCol).Value). _
Value = Cells(X, StartCol + 1).Value
Next
Columns(StartCol + 1).Clear
Application.ScreenUpdating = True
End Sub

Obvious (now) is the the fact that you should assign your actual starting
row and column numbers to the appropriate constant above.

Rick Rothstein (MVP - Excel)