Excel 2000 formula needed 1

[jthomas666]

This is for a grade book spreadsheet. I need a formula that will look at a column of averages and return the number of averages that fall within a specified range.

So in one cell, the formula has the range 90-100 and returns the number of As, etc.

Thanks

ETA

The COUNTIF function looks like the best solution. While I can set it up to return the number of As easily enough

=COUNTIF(O5:O30,">89.5") 80 and 89

I'm still trying to determine how to establish a two-sided range--eg, averages between

 

[AnotherHiggins]

Easiest way:
[COLOR=blue white]=CountIf(O:O,">80")-CountIf(O:O,">=90")[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jthomas666]

Yep, I just figured that solution out. It's not a perfect solution, as a value such as 70 or 80 will get counted twice. But it looks like it will do.

Odd that they didn't include the ability to do value ranges, though.

 

[AnotherHiggins]

It's not a perfect solution, as a value such as 70 or 80 will get counted twice.

[attn]Not true!![/attn]

You can use [attn]>[/attn] along with [attn]>=[/attn] to determine to which range a number belongs.

Example:
=CountIf(O:O,">=70")-CountIf(O:O,">80")
[tab]70 is included in this range, but 80 is not
=CountIf(O:O,">=80")-CountIf(O:O,">90")
[tab]80 is included in this range, but 90 is not

As for why an option for ranges aren't included, I don't see a reason for it. This solution works perfectly and is arrived at by simple logic.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

actually no - if you use > & >= (or < & <=) properly, you should get no double counts

To my mind, being able to use a formula as shown IS having the functionality to do value ranges. The other way to go about this is to use VLOOKUP with a lookup table.

If you set up a table such as:

0  u
50 e
60 d
70 c
80 b
90 a

you can use the vlookup function with the 4th argument set to true to apply the grading to the score. You can then use the simpler
=countif(myRange,"a")
etc etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

LOL - John - I would've beaten you to your own reply if I hadn't bothered with the VLOOKUP option ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jthomas666]

Absolutely correct. Major brain cramp on my part. Thanks!