Overcoming Nested IF limits
 

I am trying to make a GPA calculator that also calculates the Quality Points
and the nested IFs worked fine until we added the minus grades to the mix.

Typically a course is worth 4 credits, so I am trying to get results like:
Bio 100 4crs A- (grade) 14.68 (4*3.67=Quality Points) 3.67 (GPA
Credits)
Bio 200 4crs B (grade) 12.00 (4*3.00=Quality Points) 3.00 (GPA
Credits)

So, any suggestions on how I might make the following nested IFs into a
vlookup since I have more than the allowed 7 nested IFs?

This was the formula in the Quality Points cell

=IF(F20="A",4*E20,
IF(F20="A-",3.67*E20,IF(F20="B+",E20*3.33,IF(F20="B",E20 *3,
IF(F20="B-",2.67*E20,IF(F20="C+",E20*2.33,IF(F20="C",E20 *2,
IF(F20="C-",1.67*E20,IF(F20="D",E20*1,IF(F20="F",E20*0," No Grade"))))))) GPA
Credits

This was the formula in the GPA Credits cell

=IF(OR(F20="A", F20="A-",F20="B+",F20="B", F20="B-",F20="C+",F20="C",
F20="C-",F20="D",F20="F"),E20,"No GPA Credits")

Should the VLOOKUP be written like this if I set aside a 2-colum reference
range elsewhere on the form
=IF(F20<€�€�,+F20$VLOOKUP(E20,$A33:B42,2),0)

Since I am EXTREMELY new to this type of work with EXCEL, any help would be
deeply appreciated!

Overcoming Nested IF limits
 

hi LIUPharm,

I take it you're having trouble with your formula in the Quality Points
cell. Assuming F20 must have a valid grade or be empty, try this:
=IF(F20="","No
Grade",((F20="A")*4+(F20="A-")*3.67+(F20="B+")*3.33+(F20="B")*3+(F20="B-")*2
..67+(F20="C+")*2.33+(F20="C")*2+(F20="C-")*1.67+(F20="D")*1+(F20="F")*0)*E20
)

Likewise, the formula in the GPA Credits cell could probably be simplified
to:
=IF(F20="","No GPA Credits",E20)

Cheers

--
macropod
[MVP - Microsoft Word]


"LIUPharm" wrote in message
...
I am trying to make a GPA calculator that also calculates the Quality
Points
and the nested IFs worked fine until we added the minus grades to the mix.

Typically a course is worth 4 credits, so I am trying to get results like:
Bio 100 4crs A- (grade) 14.68 (4*3.67=Quality Points) 3.67 (GPA
Credits)
Bio 200 4crs B (grade) 12.00 (4*3.00=Quality Points) 3.00 (GPA
Credits)

So, any suggestions on how I might make the following nested IFs into a
vlookup since I have more than the allowed 7 nested IFs?

This was the formula in the Quality Points cell

=IF(F20="A",4*E20,
IF(F20="A-",3.67*E20,IF(F20="B+",E20*3.33,IF(F20="B",E20 *3,
IF(F20="B-",2.67*E20,IF(F20="C+",E20*2.33,IF(F20="C",E20 *2,
IF(F20="C-",1.67*E20,IF(F20="D",E20*1,IF(F20="F",E20*0," No Grade")))))))
GPA
Credits

This was the formula in the GPA Credits cell

=IF(OR(F20="A", F20="A-",F20="B+",F20="B", F20="B-",F20="C+",F20="C",
F20="C-",F20="D",F20="F"),E20,"No GPA Credits")

Should the VLOOKUP be written like this if I set aside a 2-colum reference
range elsewhere on the form
=IF(F20<€�€�,+F20$VLOOKUP(E20,$A33:B42,2),0)

Since I am EXTREMELY new to this type of work with EXCEL, any help would
be
deeply appreciated!

Overcoming Nested IF limits
 

THANK YOU! THANK YOU! THANK YOU!

"macropod" wrote:

hi LIUPharm,

I take it you're having trouble with your formula in the Quality Points
cell. Assuming F20 must have a valid grade or be empty, try this:
=IF(F20="","No
Grade",((F20="A")*4+(F20="A-")*3.67+(F20="B+")*3.33+(F20="B")*3+(F20="B-")*2
..67+(F20="C+")*2.33+(F20="C")*2+(F20="C-")*1.67+(F20="D")*1+(F20="F")*0)*E20
)

Likewise, the formula in the GPA Credits cell could probably be simplified
to:
=IF(F20="","No GPA Credits",E20)

Cheers

--
macropod
[MVP - Microsoft Word]


"LIUPharm" wrote in message
...
I am trying to make a GPA calculator that also calculates the Quality
Points
and the nested IFs worked fine until we added the minus grades to the mix.

Typically a course is worth 4 credits, so I am trying to get results like:
Bio 100 4crs A- (grade) 14.68 (4*3.67=Quality Points) 3.67 (GPA
Credits)
Bio 200 4crs B (grade) 12.00 (4*3.00=Quality Points) 3.00 (GPA
Credits)

So, any suggestions on how I might make the following nested IFs into a
vlookup since I have more than the allowed 7 nested IFs?

This was the formula in the Quality Points cell

=IF(F20="A",4*E20,
IF(F20="A-",3.67*E20,IF(F20="B+",E20*3.33,IF(F20="B",E20 *3,
IF(F20="B-",2.67*E20,IF(F20="C+",E20*2.33,IF(F20="C",E20 *2,
IF(F20="C-",1.67*E20,IF(F20="D",E20*1,IF(F20="F",E20*0," No Grade")))))))
GPA
Credits

This was the formula in the GPA Credits cell

=IF(OR(F20="A", F20="A-",F20="B+",F20="B", F20="B-",F20="C+",F20="C",
F20="C-",F20="D",F20="F"),E20,"No GPA Credits")

Should the VLOOKUP be written like this if I set aside a 2-colum reference
range elsewhere on the form
=IF(F20<€�€�,+F20$VLOOKUP(E20,$A33:B42, 2),0)

Since I am EXTREMELY new to this type of work with EXCEL, any help would
be
deeply appreciated!