MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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

I1 through Ix should have the control-shift-enter

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
to get the formula to use in Column H.


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.