Grade book average formula

[Will in SF]

Hi.

I need a formula that averages grades with the following values...
A=4,B=3,C=2,D=1,F=0. I am entering the actual letter grades in the cell.

 

[Biff]

Hi!

You'd be better off just using a number system instead of using a letter
system then converting to a number system.

But anyhow, try this formula entered as an array using the key combo of
CTRL,SHIFT,ENTER:

=AVERAGE(IF(A1:J1="A",4,IF(A1:J1="B",3,IF(A1:J1="C",2,IF(A1:J1="D",1,IF(A1:J1="F",0))))))

Biff

 

[JE McGimpsey]

One way:

=(COUNTIF(A1:Z1,"A")*4+COUNTIF(A1:Z1,"B")*3+COUNTIF(A1:Z1,"C")*2
+COUNTIF(A1:Z1,"D"))/COUNTA(A1:Z1)

 

[Dana DeLouis]

If you use Data | Validation to ensure that you only have A,B,C,D or F (& no
blanks), then perhaps this Array formula...

=AVERAGE(MOD(2530,CODE(A1:A5)-59))

HTH :>)

 

[Roger Govier]

Hi Dana

Very neat solution!!
I had been playing about with
{=AVERAGE(69-CODE(J1:J5))}
but the lack of "E" in the range of Grades being used gave me the wrong
results, as "F ended up as a -1.

I can't work out how you came up with the 2530 to use as the dividend in
the formula?

Regards

Roger Govier

 

[Will in SF]

Thanks to everyone that posted to this question.

I am going to check the formulas out.

Biff- I tried yours but it didn't work. I don't know what you meant by
(array using shift control delete keys.

So here's how this information is listed on a report that I have to avg. the
grades for.

Col. A Col. B Col C. Col. D. Col E Col.
F Col. g GPA
Student Name I.D. Grade Grade Grade Grade Grade

Students have anywhere from 1 class to 5 class, meaning averaging based on
letter grades advised and based on different number of classes taken.

Any suggestions as to how I can do this.

thanks.