Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Sub-Totals in Excel | Excel Discussion (Misc queries) | |||
Nested IF statement with VLOOKUP | Excel Discussion (Misc queries) | |||
overcoming nested IF limitations...with VBA? | Excel Worksheet Functions | |||
Nested Subtotals in Excel 2002 | Excel Discussion (Misc queries) | |||
Why are my nested sub-totals are displaying incorrectly? | Excel Discussion (Misc queries) |