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

Take a look VLOOKUP function in Help menu


"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

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

"Teethless mama" wrote:

Take a look VLOOKUP function in Help menu


"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

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

"Cindy" wrote:

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

"Teethless mama" wrote:

Take a look VLOOKUP function in Help menu


"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

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

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

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

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,









"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

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+AF 4+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)
--
David Biddulph

"Cindy" wrote in message
...
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
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

Cindy,

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

cil

=IF(A189,"A",IF(A179,"B", IF(A169,"C",IF(A159,"D","F"))))


"SeventFloorProfessor"
wrote in message ...
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
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