Expanding a formula

[Bedford]

I have a formula that works fine; =IF(J9="None","0",IF(J9="Single","1",IF(J9="Double","2"))).
Now I wish to expand exponentially to return up to a "6". From "Double" continuing to "Triple","3" and so on. I've tried repeating the original parts of the formula only to find it returning an error.
My attempt that returns an error looks like; =IF(J9="None","0",IF(J9="Single","1",IF(J9="Double","2",IF(J9=”Triple”,”3”,IF(J9=”Quad”,”4”,IF(J9=”Quin”,”5”,IF(J9=”Sext”,”6”)))))))
However it returns error; #NAME?
Any help with solving this would be appreciated.
Thank you.

 

[etaf]

You need to change the ""

” instead of "

notice the difference
you need a false
=IF(J9="None",0,IF(J9="Single",1,IF(J9="Double",2,IF(J9="Triple",3,IF(J9="Quad",4,IF(J9="Quin",5,IF(J9="Sext",6,"not meet")))))))

Also Number in "" are TEXT and not numbers

so i left out the ""
But if you want text

=IF(J9="None","0",IF(J9="Single","1",IF(J9="Double","2",IF(J9="Triple","3",IF(J9="Quad","4",IF(J9="Quin","5",IF(J9="Sext","6","not meet")))))))

 

[Skybluekid]

Try:

=IFS(J9="None","0",J9="Single","1",J9="Double","2",J9="Triple","3",J9="Quad","4",J9="Quin","5","Sext","6",TRUE,"Not Applicable")

Hope this Helps.

 

[Bedford]

You need to change the ""

” instead of "

notice the difference
you need a false
=IF(J9="None",0,IF(J9="Single",1,IF(J9="Double",2,IF(J9="Triple",3,IF(J9="Quad",4,IF(J9="Quin",5,IF(J9="Sext",6,"not meet")))))))

Also Number is "" are TEXT and not numbers

so i left out the ""
But if you want text

=IF(J9="None","0",IF(J9="Single","1",IF(J9="Double","2",IF(J9="Triple","3",IF(J9="Quad","4",IF(J9="Quin","5",IF(J9="Sext","6","not meet")))))))

Etaf, interesting that with the original short version of the formula the numbers and text are in "", complicated. Can you explain the use of "not meet" at the end of the formula?
Thank you.

 

[etaf]

if the cell does not equal any of your words - then you get a FALSE - i just replaced with some text
"not Meet"
could have said anything really
But if not included - then you will just get FALSE - which can be confusing and sometimes a better term to explain why can help

Notice Skybluekid - added "Not Applicable" for the same reason

did you edit the formula in a text document , word notepad or directly on excel

I Notice you have a Mac

and i use
TextEdit

Which does do this change of "" - real pain - if editing formula
and because of that i often see the mane error - then edit in excel formula bar

 

[Bedford]

if the cell does not equal any of your words - then you get a FALSE - i just replaced with some text
"not Meet"
could have said anything really
But if not included - then you will just get FALSE - which can be confusing and sometimes a better term to explain why can help

Notice Skybluekid - added "Not Applicable" for the same reason

did you edit the formula in a text document , word notepad or directly on exel
Notice you have a Mac

and i use
TextEdit

Which does do this change of "" - real pain - if editing formula

I understand. I did edit it in MS Word, textedit sounds like a better solution.
Thank you,
Doug.

 

[etaf]

No textedit - produces the same problem
see here is a formula in TextEdit

=If( A2=“fred”,1.”noway”)

same issue

 

[Bedford]

No textedit - produces the same problem
see here is a formula in TextEdit

=If( A2=“fred”,1.”noway”)

same issue

I see, then it is best to edit directly in excel, I sometimes do so using; '= to start the formula, maybe this is the best solution.
Thank you again Etaf!

 

[etaf]

actually i looked into a bit more

change the document type to
Plain Text

that then gives

=If(a2="A","1","2")

which work

TextEdit
Preferences
Plain Text

Mine was set to Rich text

 

[Bedford]

actually i looked into a bit more

change the document type to
Plain Text

that then gives

=If(a2="A","1","2")

which work

TextEdit
Preferences
Plain Text

Mine was set to Rich text

Interesting. I've recently purchased a windows laptop as I am considering using Access, it is not supported by mac. The new windows machine, dell xps 17, the trackpad is terrible compared to the mac, maybe that's why they included a touch screen which I cannot see myself ever using. Windows 11, still not mac.