Changing GPA to Ltr Grade

[Cindy]

I have this spreadsheet that merges onto a Transcript in Word….We have
Student Names, Class Dates, Grades for Classes per Terms, etc.….They have
been manually figuring the GPA,,,,I set up some formulas that has helped, but
need help on the more difficult ones….someone helped me last week with the
following formula (finding and matching the Grade to return a GPA.
THANKS…….Now I have been asked to find and match the Grade or GPA and return
a Ltr Grade on the Transcript as well and I need help again…..

The following formula matches the Grade to the GPA:

formula is: =SUMPRODUCT(--($AR$2:$AR$37=D3),$AS$2:$AS$37)

I tried using the same formula, just changing the Column numbers, but it
didn’t work…Can you help me? I don’t know if the problem is number to
text….(numeric to alpha)

lookup table sample is:

AR AS AT
Grade GPA Ltr Grade
2 100 4.0 A
3 99 4.0 A
4 98 4.0 A
5 97 4.0 A
6 96 3.7 A-
7 95 3.7 A-
8 94 3.5 B+
9 93 3.5 B+
10 92 3.0 B
11 91 3.0 B
12 90 3.0 B
13 89 2.7 B-
14 88 2.7 B-
15 87 2.7 B-
16 86 2.5 C+
17 85 2.5 C+

Please write back.....
Thanks,
Cindy

 

[Teethless mama]

Take a look VLOOKUP function in Help menu

 

[Cindy]

Tried this, either I don't understand how to create the formula or this isn't
the correct one to use.
HELP!

 

[Tom Hutchins]

If the Grade is in D3 and the lookup range is in AR2:AT17, this VLOOKUP
formula should return the letter grade:

=VLOOKUP(D3,$AR$2:$AT$17,3,FALSE)

Hope this helps,

Hutch

 

[SeventFloorProfessor]

There's a way to do it using HLOOKUP, but you have to create a table that
assigns values (A+ is a 97 & a 4.0, for instance), so I don't know if you
want to do that. If you do, let me know, and I'll try to explain it.

=IF(E7<>"",HLOOKUP(E7/100,GradeValue,2),"")

 

[Cindy]

O.K., now let me explain my delima...Column AN is a total of all the grades
divided by the number of grades available (also need to see if I'm using the
correct formula there---I have
=SUM(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4+AF4+AG4+AH4)/19 --
sometimes we only have grades for First Term which is the first 6 D thru I
and we have to change the 19 to 6 -- What's the correct formula for that
cell???) NOW back to the first question: the Column AN which is the total of
the grades divided by the number of grades sometimes gives a whole number
which the formula =VLOOKUP(AN4,$AQ$2:$AS$37,3,FALSE) works
wonderfully.....BUT sometimes the cell has the number 91.94736842....I have
Formated the cell to a number with 0 decimal places which returns a 92
(that's what shows in the cell,,,,,,however when I use the VLOOKUP formula it
gives me #N/A -- the Function Arguments Lookup_value = 91.94736842......I
have even tried adding another column and doing the =AN11 (for example) but
it stills pulls the same number of 91.94736842......
HELP!

 

[xlm]

Hi Cindy

try this formula to find the desire Grade return
a Ltr Grade on your target cell. You may need to change the cell's references
to suit yours.

=INDEX($AT$2:$AT$20,MATCH(D3,$AR$2:$AR$100,0))

Does this do what you want?

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

 

[David Biddulph]

Well, to start with, you don't need SUM in that formula.
=SUM(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4+AF4+AG4+AH4)/19
is the same as
=(D4+E4+F4+G4+H4+I4+N4+O4+P4+Q4+R4+S4+X4+Z4+AA4+AF4+AG4+AH4)/19
Another option is =SUM(D4:I4,N4:S4,X4,Z4:AA4,AF4:AH4)/19
Interesting that you've divided by 19, rather than by 18
If you wanted an average of the non-blank entries in the range, you might
want to try =AVERAGE(D4:I4,N4:S4,X4,Z4:AA4,AF4:AH4)

 

[CIL]

Cindy,

Give this a shot. You may have to change the values to meet your needs.

cil

=IF(A1>89,"A",IF(A1>79,"B", IF(A1>69,"C",IF(A1>59,"D","F"))))