Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Grade book average formula
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. |
#2
|
|||
|
|||
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 "Will in SF" <Will in wrote in message ... 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. |
#3
|
|||
|
|||
One way:
=(COUNTIF(A1:Z1,"A")*4+COUNTIF(A1:Z1,"B")*3+COUNTI F(A1:Z1,"C")*2 +COUNTIF(A1:Z1,"D"))/COUNTA(A1:Z1) In article , "Will in SF" <Will in wrote: 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. |
#4
|
|||
|
|||
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 :) -- Dana DeLouis Win XP & Office 2003 "Will in SF" <Will in wrote in message ... 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. |
#5
|
|||
|
|||
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 Dana DeLouis wrote: 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 :) |
#6
|
|||
|
|||
Grade book average formula
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. "Dana DeLouis" wrote: 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 :) -- Dana DeLouis Win XP & Office 2003 "Will in SF" <Will in wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for average recorded blood pressure readings in 1 column . | Excel Worksheet Functions | |||
moving the formula "average" over one column in a macro | Excel Worksheet Functions | |||
formula to calculate the average of a range basing on condition | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
What is the formula for weighted average? | Excel Worksheet Functions |