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=”Annua
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
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=”Annua
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
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="Annua
Your original had ” rather than "
” has ASCII code 148 whereas
" has ASCII code 34
Try this:
=IF(J13="6 Monthly",180,IF(J13="Annua
Your original had ” rather than "
” has ASCII code 148 whereas
" has ASCII code 34
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,FA
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
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,FA
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
You're welcome! Glad it worked as desired.
Thanks and you too!
Thanks and you too!
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!
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!
ASKER
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
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
Try this...
Open in new window
Excel-Example-for-Nested-If-Functio.xlsx