Frequency Table

[Luke McTighe]

Ok, Instead of raw data I have a frequency table, and I want to do a
statistical analysis. For example 100 students take a test with 10
questions and their scores are distributed as follows:
Score Frequency
1 0
2 3
3 5
4 9
5 10
6 14
7 16
8 19
9 15
10 9

How do I construct formulas to give me the average, stdev and
specified percentiles. This may be an easy one and maybe I'm just
missing it.

 

[Bob Cresto]

Here are the results when you use the Analysis Toolpack add-in under
Tools, and select Descriptive Statistics.

Score Frequency

Mean 5.5 Mean 10
Standard Error 0.957427108 Standard Error 1.926424437
Median 5.5 Median 9.5
Mode #N/A Mode 9
Standard Deviation 3.027650354 Standard Deviation 6.091888961
Sample Variance 9.166666667 Sample Variance 37.11111111
Kurtosis -1.2 Kurtosis -0.8899707
Skewness 0 Skewness -0.206419559
Range 9 Range 19
Minimum 1 Minimum 0
Maximum 10 Maximum 19
Sum 55 Sum 100
Count 10 Count 10
Confidence Level(95.0%) 2.16585224 Confidence Level(95.0%) 4.35787816

 

[Luke McTighe]

Thanks, but I don't think that's it. The results below take each
column as a separate list of numbers and provide the Mean, Median,
StdDev, etc... for each list. In my table:
Score Frequency
1 0
2 3
3 5
4 9
5 10
6 14
7 16
8 19
9 15
10 9
The first column is the test scores, the second column is the
frequency of each score in a population of 100 students tested. If I
had the results in a single column 100 lines long (with a score of 1
appearing 0 times, 2 appearing 3 times, etc...), then I would be able
to use the data analysis tools or a statistical formula in the formula
bar. I'm trying to figure out an easy way to either convert my table
into the raw data, or construct a set of nested formulas that will
give me the answers I'm looking for.

 

[Leo Heuser]

Luke

Here's an array formula, that will return your original dataset:

Assuming data in A2:B11 (no blanks. testscores in A2:A11 and freq in B2:B11)

(There has to be a cell above the starting cell (here C1), and that cell
must not contain a number!)

In C2:

=IF(ROW()-ROW($C$2)>=SUM($B$2:$B$11),"No more!", INDEX($A$2:$A$11,
MATCH(MIN(IF(COUNTA($C$1:C1)<MMULT((ROW($B$2:$B$11)>=
TRANSPOSE(ROW($B$2:$B$11)))+0,$B$2:$B$11),MMULT((ROW($B$2:$B$11)>=
TRANSPOSE(ROW($B$2:$B$11)))+0,$B$2:$B$11))),MMULT((ROW($B$2:$B$11)>=
TRANSPOSE(ROW($B$2:$B$11)))+0,$B$2:$B$11),0)))

Enter the formula with <Shift><Ctrl><Enter>, also if edited later. If done
correctly Excel will display the formula in the formula bar enclosed in
curly brackets { }. Don't enter these brackets yourself.

Copy C2 down as far as necessary (at least the number of cells, matching
the sum of the numbers in B2:B11) with the fill handle (the little square in
the lower right corner of the cell).

C2:C?? will display the original dataset

 

[Leo Heuser]

Slight correction:
If you use the below formula, C1 must be blank.
If you replace COUNTA (numbers and text allowed in
column A) with COUNT (only numbers allowed in column A),
you can have a text heading in C1)

LeoH

 

[Luke McTighe]

Thanks, that definitely works. I hate to ask, but could you explain
what is going on in this formula?

 

[Leo Heuser]

Thanks, that definitely works. I hate to ask, but could you explain
what is going on in this formula?

You're welcome and thanks for the feedback

From your original data list

0
3
5
9
10
14
16
19
15
9

create this accumulated sequence by adding
elements (First element, first+second, first+second+third, etc)

0
3
8
17
27
41
57
76
91
100

This sequence shows, where there is a change of
element in testscores. It is after 0, i.e. position 1,
after 3, i.e. position 4, after 8 (tastes delicious ,
i.e. position 9, etc. (try comparing these numbers
to the generated list of your original data)

$C$1:C1 acts as a counter, i.e. C1 (not $C$1) is
increased by one (C2, C3, etc.) for each cell you drag
the formula down.

The formula now compares the counter with the elements
in the accumulated sequence to see, when the next element
is due for insertion. Until then it inserts the element at hand.

I hope this gives an indication of, what's going on.