Excel: Counting data in ranges

[stc]

Part of my spreadsheet lists percentages. So C1 = 50%, C2 = 47%, C3 = 42%, C4 = 32%, C5 = 27%, and so on. I want to count the number of cells which fall into ranges, for example, 50%, 40-49%, 30-39% and so on. I have used countif(c1:c10,"=50%") for counting the cells with 50%, but I can't figure out how to count the ranges. Any suggestions? Thanks!

 

[GlennUK]

Have a look at the FREQUENCY function and look at the help for it.


That should give you some ideas.

Cheers, Glenn.

 

[xlbo]

Just use some logic with the countif - for example

=countif($C$1:$C$10,"<50%")-countif($C$1:$C$10,"<40%")

will give you your count between 40 and 49 %

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ETID]

I work with Histograms on a daily basis...so I am a big fan of GlennUK's suggestion.

Look in the help on the frequency function, and read carefully( it's not difficult, but there are some useful tips on how to get the most out of it ).

When making a list of ranges (bin / class)...

use this not this
30% 30-39%
40% 40-49%
50% 50-59%
etc. etc.


The frequency function is an array formula...so don't forget to highlite the cells Adjacent to your bin or class range of percentages and to use Ctrl+Shift+Enter to apply this formula.