Calculating the percentage of times a value apperas in a data set.

Posted by Paul Sterk on September 21, 2000 1:04 PM

I have a range of values that is quite large and I want to be able to calculate
how many times a each value appears within the range of numbers. I thought that the
frequency function would work but I'm having difficulty getting it to work. My results
need to be displayed as a sort of distribution that shows the percentage of each value
within the range.

Posted by Tim Francis-Wright on September 21, 2000 2:47 PM

There's probably an easier way to do this than
the way I'm suggesting, but here's a go at it:

I assume that the dataset is in a range called
DATA, in column B, starting in B1. Prepare
three empty columns (I'll assume G, H, and I).
From G1 through Gx (where x is the number of
cells in the dataset), use the control-shift-enter formula
=IF(COUNTIF($B$1:B1,B1)=1,B1,"")

For sanity's sake, let's call G1 through Gx DATA2.
You can keep column G hidden.

H1 through Hx should have the (normal) formula
=IF(ISERR(SMALL(Data2,ROW(G1))),0,SMALL(Data2,ROW(G1)))

I1 through Ix should have the control-shift-enter
formula
=SUM(IF(H1=Data,1,0))/COUNT(Data)

Column G extracts unique entries from the
dataset. Column H sorts the data from smallest
to largest, and Column I does the math.

I've assumed, of course, that you have numeric
data. If you have alphabetic data, you should
check out Chip Pearson's page
http://www.cpearson.com/excel/noblanks.htm
to get the formula to use in Column H.

HTH

Posted by Paul Sterk on September 22, 2000 12:49 PM

Thanks for your response. I'm now wondering if I should have provided a little more info. Thete are appr.
3000 entries in the one column. They are numeric, but they are a decimal figure.