SOLVED

#NAME? error from nested formula

Copper Contributor

I keep getting a #NAME? error any time I input any number greater than 6 in cell A1 from the following nested IFAND formula. 

=

IF(AND(A1>0, A1<=3), "5",

IF(AND(A1>3, A1<=6), "10",

IF(AND(A1>6, A1<=9), “15”,

IF(AND(A1>9, A1<=12), “20”,0))))

Where is my mistake? 

7 Replies
best response confirmed by Carlin75 (Copper Contributor)
Solution

@Carlin75 

Your formula uses curly quotes instead of straight quotes around 15 and 20. Change them to straight quotes.

By the way, do you need the quotes at all? It appears to me that you want to return numbers, not text strings.

@Carlin75 

Most probably that's apostrophes around 15 and 20, change on "normal" ones

=IF(AND(A1>0, A1<=3), "5",
IF(AND(A1>3, A1<=6), "10",
IF(AND(A1>6, A1<=9), "15",
IF(AND(A1>9, A1<=12), "20",0))))
You are using the wrong double quotes around 15 and 20.
In fact you can get rid of all double quotes since you want a number for your output and not a text.

@Detlef Lewin 

<laugh>

(The forum appears to delete emojis)

:face_with_tears_of_joy:

:)

@Carlin75 I changed from a nested IFAND formula to a IFS formula. Much easier. Thank you!

 

I typed the formula in word and tried to copy/paste into excel. Your comment made me realize the quotation marks are different. I ended up using an IFS formula instead.
1 best response

Accepted Solutions
best response confirmed by Carlin75 (Copper Contributor)
Solution

@Carlin75 

Your formula uses curly quotes instead of straight quotes around 15 and 20. Change them to straight quotes.

By the way, do you need the quotes at all? It appears to me that you want to return numbers, not text strings.

View solution in original post