Nested If Formula - Using Text to get a number as a result

I am having an issue with a nested IF Formula. In column J, the result will either be 6 monthly, Annually, 2 yearly, 5 yearly or No data.  

I have tried the folowing formula in column K as I want to use to result from column J (eg) 180, 365 etc as part of a formula to calculate the actual renewal date

=IF(J13=”6 Monthly”,180,IF(J13=”Annually”,365,IF(J13=”2 Yearly”,730,IF(J13=”5 Yearly”,1825,”n/a”))))

When I use this formula, I get the result #NAME?. I have tried to evaluate the formula but it does not show that there are any errors with my formula.

I know I can have a drop down in column K with each of the time options available and then use this cell reference in my formula but I am sure that there is a way to get the result I want without having to use a drop down option.

Any assistance would be greatly appreciated.
Thanks
Dot
Excel-Example-for-Nested-If-Functio.xlsx
Dot GlindemannAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You used wrong types of quotes, you need to use the double quotes instead...

Try this...
=IF(J13="6 Monthly",180,IF(J13="Annually",365,IF(J13="2 Yearly",730,IF(J13="5 Yearly",1825,"n/a"))))

Open in new window

Excel-Example-for-Nested-If-Functio.xlsx
0
Rob HensonFinance AnalystCommented:
I believe it relates to your use of double quotes, they are not true double quotes.

Try this:
=IF(J13="6 Monthly",180,IF(J13="Annually",365,IF(J13="2 Yearly",730,IF(J13="5 Yearly",1825,"n/a"))))

Your original had ” rather than "
” has ASCII code 148 whereas
" has ASCII code 34
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may also use the following formula to get the same output...

=IFERROR(CHOOSE(MATCH(J13,{"6 Monthly","Annually","2 Yearly","5 Yearly"},0),180,365,730,1825),"N/A")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
Alternatively, you already have the Dropdown options in a list, use that list for a vlookup.

On your Data Validation sheet enter the number of days for each period in column B then use this formula on your name sheet:

=VLOOKUP(J13,'Data Validation'!$A$1:$B$4,2,FALSE)

If the lookup does not match a value the error result will be #N/A. If you specifically want just "n/a" then use:

=IFERROR(VLOOKUP(J13,'Data Validation'!$A$1:$B$4,2,FALSE),"n/a")
0
Rob HensonFinance AnalystCommented:
Or this can be copied down the whole of column K even if J is blank:

=IF(J13="","",IFERROR(VLOOKUP(J13,'Data Validation'!$A$1:$B$4,2,FALSE),"n/a"))
0
Dot GlindemannAuthor Commented:
Thank You Subodh and Rob,

Both suggestions were very helpful but I do not have any experience yet using VLookUp (Rob) so for now I will work with what I know.

This has helped me very much.

I hope you both have a great weekend.

Thanks Dot
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad it worked as desired.
Thanks and you too!
0
Rob HensonFinance AnalystCommented:
Glad to help.

I suspect there are a number of articles already written but feel free to raise other questions to expand your knowledge of VLOOKUP.

Bank Holiday weekend so will be a long one!
0
Dot GlindemannAuthor Commented:
Thanks Rob,

I decided to work on learning more about VLOOKUP and I tried your suggestions and it worked. So I am going to spend more time on this subject. Hope your Bank Holiday is relaxing and fun, Thanks Dot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.