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

Dot Glindemann
Dot Glindemann used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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
Rob HensonFinance Analyst

Commented:
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
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

Commented:
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")
Rob HensonFinance Analyst
Commented:
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"))

Author

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
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! Glad it worked as desired.
Thanks and you too!
Rob HensonFinance Analyst

Commented:
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!

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial