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