Nested IF Function returns #NAME? Error

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

 

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

 

[bw]

Sheeloo,
i have tried it with the extra parenthesis, same error results.

 

[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. 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","")))))

 

[bw]

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

 

[Sheeloo]

Did you copy and paste the formula I had in my post?

Pl. paste what you now have.

 

[Harlan Grove]

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

 

[John C]

Thanks for the feedback :O)
--
** John C **

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