Nested IF Function returns #NAME? Error


B

bw

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?
 
Ad

Advertisements

S

Sheeloo

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.
 
J

John C

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('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))
 
T

T. Valko

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","")))))
 
B

bw

John,
the IF function gave me a #REF! error, but the VLOOKUP fixed the problem,
THANK YOU!!!
 
Ad

Advertisements

H

Harlan Grove

bw said:
=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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top