Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I fix a GPA formula with a null value
I had posted a question on how to fix a GPA formula with a null value. This
was the formula given: =IF(D17="','",Lookup(D17,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E27 But the problem is that it is not multiplying in whole numbers for b,c,d. I get the answers of b=7.4, c=5.4, d=3.4 so can someone please help me. Also somewhere in the formula I have lost part of the formula because now I get an "FALSE" statement. Can someone please help me. Can you also send replies to: |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I fix a GPA formula with a null value
=IF(D17="","",LOOKUP(D17,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E27)
you has an extra apostrophe at the start of the IF function to get whole numbers for b, c and d you need to change the numbers in your hard coded formula, look at the second position of the numbers, that is for b. If you don't want 3.7 change it to whatever you want. The same goes for the rest -- Regards, Peo Sjoblom "JohnG" wrote in message ... I had posted a question on how to fix a GPA formula with a null value. This was the formula given: =IF(D17="','",Lookup(D17,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E27 But the problem is that it is not multiplying in whole numbers for b,c,d. I get the answers of b=7.4, c=5.4, d=3.4 so can someone please help me. Also somewhere in the formula I have lost part of the formula because now I get an "FALSE" statement. Can someone please help me. Can you also send replies to: |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I fix a GPA formula with a null value
"Peo Sjoblom" wrote...
=IF(D17="","",LOOKUP(D17,{"a","a-","b+","b","b-","c+","c","c-", "d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})* E27) you has an extra apostrophe at the start of the IF function to get whole numbers for b, c and d you need to change the numbers in your hard coded formula, look at the second position of the numbers, that is for b. If you don't want 3.7 change it to whatever you want. The same goes for the rest .... "JohnG" wrote in message I had posted a question on how to fix a GPA formula with a null value. This was the formula given: .... But the problem is that it is not multiplying in whole numbers for b,c,d. I get the answers of b=7.4, c=5.4, d=3.4 so can someone please help me. .... The problem not yet addressed is that LOOKUP expects its lookup row or column to be sorted in ascending order. The text values "a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f" are NOT in ascending text order. They'd need to be ordered as "a","a-","b","b-","b+","c","c-","c+","d","d-","d+","f" and the formula would need to be rewritten as =IF(D17="","",LOOKUP(D17,{"a","a-","b","b-","b+","c","c-","c+", "d","d-","d+","f";4,3.7,3,2.7,3.3,2,1.7,2.3,1,0.7,1.3,0}) *E27) or you'd need to switch from LOOKUP to HLOOKUP. =IF(D17="","",HLOOKUP(D17,{"a","a-","b+","b","b-","c+","c","c-", "d+","d","d-","f";4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0},2,0)* E27) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
COUNTIF says Null = Blank but Blank < Null | Excel Worksheet Functions | |||
ISO smallest passive / do nothing formula for formatting null.. | Excel Discussion (Misc queries) | |||
cell value based on null/not null in another cell | Excel Worksheet Functions | |||
how to enter a null cell value in a formula | Excel Worksheet Functions |