Formula Help

D

Diana HIgh School

I need help formatting several fromulas.

The first is: I have a column of % (test grades). I would like to have
Excel calculate the # of grades that fall within certain perameters, such as
90-100, ,80-89 etc. How can I write a formula to calculate this? I've tried
variations of Count with conditions, but I obviously haven't figured out the
right thing to do.

Second: When computing a column to obtain the AVERAGE, I'd like EXCEL to
ignore the zeros (0), as those students would have been absent.

It is Wednesday night, ,just after 9:00. If you replay aoutside of an hour
from now, would you please contact me at [email protected]. I don't use
hotmail very much. A BILLION thanks in advance!!!
 
T

tjtjjtjt

For your first issue, , enter the Bottom part of each grade range into a
cell. Ex:
D1 60
D2 70
D2 80
D4 90
D5 100

Then, select a group of Cells equal to the number of grade ranges. In my
example, I would select something like E1:E5.

Then use this Formula (The_Grades represents the cells that contain the
grades:
=Frequency(The_Grades,D1:D5)
Press Ctrl+Shift+Enter to calculate this formula.


For your second issue:
=AVERAGE(IF(A1:A20<>0,A1:A20,FALSE))
You also use Ctrl+Shift+Enter to enter this Formula

tj
 
D

duane

if grades in column A try this

=countif(a1:a1000,">=90") for "A"

=countif(a1:a1000,>=80) - cell with count of "A"

etc

for average

try =average(if(a1:a1000>0),a1:a1000)

entered as an array - control+shift+enter allat onc
 
D

Diana High School

"Elemetary", I am sure I am "'my dear Watson", at best... This is actually
what I am trying to do
Teacher:
Block: BENCHMARK:
Test Name:
Date:
# Items:
Points: #DIV/0!
First Last # Correct % Correct

#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Average #DIV/0! #DIV/0!
Max 0 #DIV/0!
Min 0 #DIV/0!


Percent 90-100
Percent 80-89
Percent 70-79
Perceny 60-69
Percent 50-59
Percent 40-49
Percent 30-39
Percent 20-29
Percent 10-19
Percent 0-9

Obviously the cut and Paste didn't work well...If you will contact me at
[email protected], I will send the document and perhaps you can help. I tried
what I interpreted as your suggestion, but I failed. Many thanks, regardless
of your concern and time to offer assistance. Hope you will contact me
directly so I can atach worksheet.

Thanks, regardless,
Diana
 
D

Diana HIgh School

"Elemetary", I am sure I am "'my dear Watson", at best... This is actually
what I am trying to do
Teacher:
Block: BENCHMARK:
Test Name:
Date:
# Items:
Points: #DIV/0!
First Last # Correct % Correct

#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Average #DIV/0! #DIV/0!
Max 0 #DIV/0!
Min 0 #DIV/0!


Percent 90-100
Percent 80-89
Percent 70-79
Perceny 60-69
Percent 50-59
Percent 40-49
Percent 30-39
Percent 20-29
Percent 10-19
Percent 0-9

Obviously the cut and Paste didn't work well...If you will contact me at
[email protected], I will send the document and perhaps you can help. I tried
what I interpreted as your suggestion, but I failed. Many thanks, regardless
of your concern and time to offer assistance. Hope you will contact me
directly so I can atach worksheet.

Thanks, regardless,
Diana
 
Top