Formatting the cell only changes how it is displayed, not the underlying
value in the cell. In the new column you mentioned, use =ROUND(AN11,0), for
example, to round the grade in AN11 to a whole number. Then do your VLOOKUP
using the new column value instead of the raw value in column AN.

Hope this helps,

Hutch

"Cindy" wrote:

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!

"SeventFloorProfessor" wrote:

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" wrote:

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
didnt work€¦Can you help me? I dont 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