| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function returns #NAME? Error
Help- i can't figure out why this is not working. the error i am receiving is
centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
There is one left parenthesis "(" missing after each IF except for the first
one. Add ( after each IF except the first and then add four ")" at the end... =IF(('Comparison Chart'!H63="Select One"),"Select One", IF(('Comparison Chart'!H63="Monthly"),"Monthly", IF(('Comparison Chart'!H63="2.5% DiscountPaid Quarterly"),"Quarterly",IF(('Comparison Chart'!H63="5.0% Discount PaidSemiAnnually"),"SemiAnnually",IF(('Comparison Chart'!H63="12.0% Discount PaidAnnually"),"Annually",""))))) You should simply set up the conditions in one column and result in another and use VLOOKUP. "bw" wrote: Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
Sheeloo,
i have tried it with the extra parenthesis, same error results. "Sheeloo" wrote: There is one left parenthesis "(" missing after each IF except for the first one. Add ( after each IF except the first and then add four ")" at the end... =IF(('Comparison Chart'!H63="Select One"),"Select One", IF(('Comparison Chart'!H63="Monthly"),"Monthly", IF(('Comparison Chart'!H63="2.5% DiscountPaid Quarterly"),"Quarterly",IF(('Comparison Chart'!H63="5.0% Discount PaidSemiAnnually"),"SemiAnnually",IF(('Comparison Chart'!H63="12.0% Discount PaidAnnually"),"Annually",""))))) You should simply set up the conditions in one column and result in another and use VLOOKUP. "bw" wrote: Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
I think you also have some of the non-standard quotates. Copy and paste this
formula: =IF('Comparison Chart'!H63="Select One","Select One",IF('Comparison Chart'!H63="Monthly","Monthly",IF('Comparison Chart'!H63="2.5% Discount Paid Quarterly","Quarterly",IF('Comparison Chart'!H63="5.0% Discount Paid SemiAnnually","SemiAnnually",IF('Comparison Chart'!H63="12.0% Discount Paid Annually","Annually",""))))) Or, if you prefer, you can setup a VLOOKUP within the cell. Bit longer, but I think more flexibility (and easier to decipher, and don't have to worry about too many nested IFs: =IF(ISNA(VLOOKUP('Comparison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)),"",VLOOKUP('Compar ison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "bw" wrote: Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
Remove all the closing parenths ")" within the formula and add them to the
very end of the formula: =IF('Comparison Chart'!H63="Select One","Select One",IF('Comparison Chart'!H63="Monthly","Monthly", IF('Comparison Chart'!H63="2.5% Discount Paid Quarterly","Quarterly",IF('Comparison Chart'!H63="5.0% Discount Paid SemiAnnually","SemiAnnually",IF('Comparison Chart'!H63="12.0% Discount Paid Annually","Annually",""))))) -- Biff Microsoft Excel MVP "bw" wrote in message ... Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63="Select One"),"Select One",IF('Comparison Chart'!H63="Monthly"),"Monthly", IF('Comparison Chart'!H63="2.5% Discount Paid Quarterly"),"Quarterly",IF('Comparison Chart'!H63="5.0% Discount Paid SemiAnnually"),"SemiAnnually",IF('Comparison Chart'!H63="12.0% Discount Paid Annually"),"Annually","") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
John,
the IF function gave me a #REF! error, but the VLOOKUP fixed the problem, THANK YOU!!! "John C" wrote: I think you also have some of the non-standard quotates. Copy and paste this formula: =IF('Comparison Chart'!H63="Select One","Select One",IF('Comparison Chart'!H63="Monthly","Monthly",IF('Comparison Chart'!H63="2.5% Discount Paid Quarterly","Quarterly",IF('Comparison Chart'!H63="5.0% Discount Paid SemiAnnually","SemiAnnually",IF('Comparison Chart'!H63="12.0% Discount Paid Annually","Annually",""))))) Or, if you prefer, you can setup a VLOOKUP within the cell. Bit longer, but I think more flexibility (and easier to decipher, and don't have to worry about too many nested IFs: =IF(ISNA(VLOOKUP('Comparison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)),"",VLOOKUP('Compar ison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "bw" wrote: Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
Did you copy and paste the formula I had in my post?
Pl. paste what you now have. "bw" wrote: Sheeloo, i have tried it with the extra parenthesis, same error results. "Sheeloo" wrote: There is one left parenthesis "(" missing after each IF except for the first one. Add ( after each IF except the first and then add four ")" at the end... =IF(('Comparison Chart'!H63="Select One"),"Select One", IF(('Comparison Chart'!H63="Monthly"),"Monthly", IF(('Comparison Chart'!H63="2.5% DiscountPaid Quarterly"),"Quarterly",IF(('Comparison Chart'!H63="5.0% Discount PaidSemiAnnually"),"SemiAnnually",IF(('Comparison Chart'!H63="12.0% Discount PaidAnnually"),"Annually",""))))) You should simply set up the conditions in one column and result in another and use VLOOKUP. "bw" wrote: Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
bw wrote...
.... =IF(('Comparison Chart'!H63=“Select One”),“Select One”, IF('Comparison Chart'!H63=“Monthly”),“Monthly”, IF('Comparison Chart'!H63=“2.5% Discount Paid Quarterly”),“Quarterly”, IF('Comparison Chart'!H63=“5.0% Discount Paid SemiAnnually”),“SemiAnnually”, IF('Comparison Chart'!H63=“12.0% Discount Paid Annually”),“Annually”,"") This isn't a syntactically valid formula. In your first IF call, you've got *2* left parentheses after IF, which is good because you have a right parenthesis just after the comparison. However, in all the other IF calls, you have a stray right parenthesis after the comparison but only one left parenthesis after IF, which Excel interprets as trying to finish those IF calls with only one argument. Since IF requires at least two arguments, that causes a syntax error. And your missing all but one of the necessary right parentheses at the end. Rewrite your formula as =IF('Comparison Chart'!H63=“Select One”,“Select One”, IF('Comparison Chart'!H63=“Monthly”,“Monthly”, IF('Comparison Chart'!H63=“2.5% Discount Paid Quarterly”,“Quarterly”, IF('Comparison Chart'!H63=“5.0% Discount Paid SemiAnnually”,“SemiAnnually”, IF('Comparison Chart'!H63=“12.0% Discount Paid Annually”,“Annually”,""))))) And don't post formulas using “ and ” rather than ". If you're using left and right double quote characters rather than the generic ASCII double quote character, that would also cause syntax errors. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested IF Function returns #NAME? Error
Thanks for the feedback :O)
-- ** John C ** "bw" wrote: John, the IF function gave me a #REF! error, but the VLOOKUP fixed the problem, THANK YOU!!! "John C" wrote: I think you also have some of the non-standard quotates. Copy and paste this formula: =IF('Comparison Chart'!H63="Select One","Select One",IF('Comparison Chart'!H63="Monthly","Monthly",IF('Comparison Chart'!H63="2.5% Discount Paid Quarterly","Quarterly",IF('Comparison Chart'!H63="5.0% Discount Paid SemiAnnually","SemiAnnually",IF('Comparison Chart'!H63="12.0% Discount Paid Annually","Annually",""))))) Or, if you prefer, you can setup a VLOOKUP within the cell. Bit longer, but I think more flexibility (and easier to decipher, and don't have to worry about too many nested IFs: =IF(ISNA(VLOOKUP('Comparison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)),"",VLOOKUP('Compar ison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "bw" wrote: Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested If returns error (too many arguments) | Excel Worksheet Functions | |||
| LINEST() function returns error. | Excel Discussion (Misc queries) | |||
| Find function returns the #VALUE! error value | Excel Discussion (Misc queries) | |||
| nested if statement returns #value error | Excel Discussion (Misc queries) | |||
| AVERAGE function returns #DIV/0! error | Excel Discussion (Misc queries) | |||
Linear Mode
