Percentage Occurrence (Possibly Frequency function)

[adrem7]

Say I have 500 different numbers from A1 to A500 and in the B column I want to show the average occurrence of each of these numbers. So for example I want:

A B
1 10
2 40
4 30
2 40
5 10
2 40
3 10
4 30
4 30
2 40

where the A column is just the number I put in and the B column is the percentage occurrence.

Now say that the A column has 5 decimal places, is there a way to get the B column to only look at the first 2 decimal places of A, so I would have something like this:

A B
1.25434 60
1.25764 40
1.25391 60
1.25205 40
1.25763 60

As 1.25 happens 60% of the time and 1.26 happens 40% of the time.

Even better would be something like this:

A B C
1.25434 1.25 60
1.25764 1.26 40
1.25391
1.25205
1.25763

I can do something along the lines of this:
=COUNTIF(A1:A500,MODE(A1:A500))/COUNT(A1:A500)

but that just takes into account the mode and so only gives the percentage of the most occurring value.

Thanks, A.

[Moo the Dog]

If your numbers are in A1:A5,

Put this in B1 and fill down: =ROUND(A1,2)
Put this in C1 and fill down: =COUNTIF($B$1:$B$5,B1)/COUNT($B$1:$B$5)*100

- Moo

[adrem7]

Awesome thanks! Is there an easy way to arrange these by percentage like in my third example?

Thanks, A.

[Moo the Dog]

I'll be honest with you, an easier way to do it would be to use a Pivot Table. See my attached file for an example.

If you absolutely need to use formulas, then with some array formulas, you can get the data you need as well.
Formula modifications: If numbers are in A2:A6:

Enter this ARRAY formula in B2 and fill down (Array formulas are applied with Ctrl + Shift + Enter, instead of just Enter):

Please Login or Register  to view this content.

Then enter this ARRAY formula in cell C2 and fill down (Again, Ctrl + Shift + Enter):

Please Login or Register  to view this content.

You can take a look at my attached sheet for both examples.

- Moo

[adrem7]

Perfect! That's just what I wanted. Thanks!