Forum Discussion
Help on a Excel Formula
So I have this formula to help calculate grades:
=IF(K3="A", 93.33,IF(K3="B",83.33, IF(K3="C",73.33,IF(K3="D",63.33))))+IF(K3="A-", 90,IF(K3="B-",80, IF(K3="C-",70,IF(K3="D-",60))))+IF(K3="A+", 100,IF(K3="B+",86.67, IF(K3="C+",76.67,IF(K3="D+",66.67))))
I would like to be able to have it calculate from A3:K3. Right now the formula is extremely long as I have to do this for every column. Is there a way to change this formula so that it could calculate the whole line?
EX.
=IF(A3:K3="A", 93.33,IF(A3:K3="B",83.33, IF(A3:K3="C",73.33,IF(A3:K3="D",63.33))))+IF(A3:K3="A-", 90,IF(A3:K3="B-",80, IF(A3:K3="C-",70,IF(A3:K3="D-",60))))+IF(A3:K3="A+", 100,IF(A3:K3="B+",86.67, IF(A3:K3="C+",76.67,IF(A3:K3="D+",66.67))))
What am I missing?
Okay so I misunderstood what you are trying to accomplish. If you want to total the corresponding scores of the grades in columns C thru K you should use SUMPRODUCT and the same table of grades/scores that I gave above. I used 'grades' and 'score' as named ranges.
The formula for the total in L13 is =SUMPRODUCT(SUMIF(grades,C13:K13,score))
(the next row is a vlookup under each column, then a sum in L14, which I used to check my work ☺)
7 Replies
Unless I misunderstand your question, it sounds like your re-typing the formula for each column? If so then just copy the formula from column K back to the A:J columns. The resulting formulas will automatically reference their respective columns, i.e. A3, B3, C3, etc.
You are correct and that is what I have right now, as seen below. I am trying to make it easier so when I add another column the formula would automatically include the next column so that I wouldn't have to keep on pasting a new set of values.
=IF(C13="A",93.33,IF(C13="B",83.33,IF(C13="C",73.33,IF(C13="D",63.33))))+IF(D13="A",93.33,IF(D13="B",83.33,IF(D13="C",73.33,IF(D13="D",63.33))))+IF(E13="A",93.33,IF(E13="B",83.33,IF(E13="C",73.33,IF(E13="D",63.33))))+IF(F13="A",93.33,IF(F13="B",83.33,IF(F13="C",73.33,IF(F13="D",63.33))))+IF(G13="A",93.33,IF(G13="B",83.33,IF(G13="C",73.33,IF(G13="D",63.33))))+IF(H13="A",93.33,IF(H13="B",83.33,IF(H13="C",73.33,IF(H13="D",63.33))))+IF(I13="A",93.33,IF(I13="B",83.33,IF(I13="C",73.33,IF(I13="D",63.33))))+IF(J13="A",93.33,IF(J13="B",83.33,IF(J13="C",73.33,IF(J13="D",63.33))))+IF(C13="A-",90,IF(C13="B-",80,IF(C13="C-",70,IF(C13="D-",60))))+IF(D13="A-",90,IF(D13="B-",80,IF(D13="C-",70,IF(D13="D-",60))))+IF(E13="A-",90,IF(E13="B-",80,IF(E13="C-",70,IF(E13="D-",60))))+IF(F13="A-",90,IF(F13="B-",80,IF(F13="C-",70,IF(F13="D-",60))))+IF(G13="A-",90,IF(G13="B-",80,IF(G13="C-",70,IF(G13="D-",60))))+IF(H13="A-",90,IF(H13="B-",80,IF(H13="C-",70,IF(H13="D-",60))))+IF(I13="A-",90,IF(I13="B-",80,IF(I13="C-",70,IF(I13="D-",60))))+IF(J13="A-",90,IF(J13="B-",80,IF(J13="C-",70,IF(J13="D-",60))))+IF(C13="A+",100,IF(C13="B+",86.67,IF(C13="C+",76.67,IF(C13="D+",66.67))))+IF(D13="A+",100,IF(D13="B+",86.67,IF(D13="C+",76.67,IF(D13="D+",63.33))))+IF(E13="A+",100,IF(E13="B+",86.67,IF(E13="C+",76.67,IF(E13="D+",66.67))))+IF(F13="A+",100,IF(F13="B+",86.67,IF(F13="C+",76.67,IF(F13="D+",66.67))))+IF(G13="A+",100,IF(G13="B+",86.67,IF(G13="C+",76.67,IF(G13="D+",66.67))))+IF(H13="A+",100,IF(H13="B+",86.67,IF(H13="C+",76.67,IF(H13="D+",66.67))))+IF(I13="A+",100,IF(I13="B+",86.67,IF(I13="C+",76.67,IF(I13="D+",66.67))))+IF(J13="A+",100,IF(J13="B+",86.67,IF(J13="C+",76.67,IF(J13="D+",66.67))))+IF(K13="A",93.33,IF(K13="B",83.33,IF(K13="C",73.33,IF(K13="D",63.33))))+IF(K13="A-",90,IF(K13="B-",80,IF(K13="C-",70,IF(K13="D-",60))))+IF(K13="A+",100,IF(K13="B+",86.67,IF(K13="C+",76.67,IF(K13="D+",66.67))))
Try using a VLOOKUP instead of all that typing...
Make sure to use the dollar signs around the range. Then copy to appropriate columns.
