Problem with my Nested IF, the Else output is incorrect

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok Here is my nested IF I tabbed it out for better reading.

Basically if the last digit of C12 ends in a value, C13 returns the equivalent

So if C12 ends with

1 then 00 shows on C13
2 then 02 shows on C13
3 then 03 shows on C13
4 then 19 shows on C13
5 then 05 shows on C13
A-Z then 02 shows on C13
[blank] should show [blank]

but blank shows '02'

Here is the formula

=IF(RIGHT(C12,1)="1",TEXT(0,"00"),
IF(RIGHT(C12,1)="2",TEXT(0,"02"),
IF(RIGHT(C12,1)="3",TEXT(0,"03"),
IF(RIGHT(C12,1)="4",TEXT(0,"19"),
IF(RIGHT(C12,1)="5",TEXT(0,"05"),
IF(OR(RIGHT(C12,1)>="A",(RIGHT(C12,1))<="Z"),TEXT(0,"02"),
IF(RIGHT(C12,1)="",TEXT(0,""))))))))

thanks for the help
 
Thanks for the response I guess I don't follow:

Are you saying my current syntax allows a blank to represent the <="Z"
condition?

Is the last line of my syntax being ignored by the OR clause using <=, =>?



JLGWhiz said:
<="Z" gives "02" Then "" is <="Z"

ssciarrino said:
Ok Here is my nested IF I tabbed it out for better reading.

Basically if the last digit of C12 ends in a value, C13 returns the equivalent

So if C12 ends with

1 then 00 shows on C13
2 then 02 shows on C13
3 then 03 shows on C13
4 then 19 shows on C13
5 then 05 shows on C13
A-Z then 02 shows on C13
[blank] should show [blank]

but blank shows '02'

Here is the formula

=IF(RIGHT(C12,1)="1",TEXT(0,"00"),
IF(RIGHT(C12,1)="2",TEXT(0,"02"),
IF(RIGHT(C12,1)="3",TEXT(0,"03"),
IF(RIGHT(C12,1)="4",TEXT(0,"19"),
IF(RIGHT(C12,1)="5",TEXT(0,"05"),
IF(OR(RIGHT(C12,1)>="A",(RIGHT(C12,1))<="Z"),TEXT(0,"02"),
IF(RIGHT(C12,1)="",TEXT(0,""))))))))

thanks for the help
Click to expand...
Click to expand...
 
First, TEXT(0,"02") is not a valid number format, so that's part of your
problem. But you don't need TEXT() at all...

Second, EVERY character is either <="Z" OR >="A", so you'll never see
your last condition.

Perhaps:

=IF(RIGHT(C12,1)="1", "01", IF(RIGHT(C12,1)="2", "02",
IF(RIGHT(C12,1)="3", "03", IF(RIGHT(C12,1)="4", "19",
IF(RIGHT(C12,1)="5", "05", IF(AND(RIGHT(C12,1)>="A",
(RIGHT(C12,1))<="Z"), "02", IF(RIGHT(C12,1)="", "", "Something
Else")))))))
 
And of course, the last RIGHT(C12,1)="" is superfluous - should be

IF(C12="", "", "Something Else")

JE McGimpsey said:
First, TEXT(0,"02") is not a valid number format, so that's part of your
problem. But you don't need TEXT() at all...

Second, EVERY character is either <="Z" OR >="A", so you'll never see
your last condition.

Perhaps:

=IF(RIGHT(C12,1)="1", "01", IF(RIGHT(C12,1)="2", "02",
IF(RIGHT(C12,1)="3", "03", IF(RIGHT(C12,1)="4", "19",
IF(RIGHT(C12,1)="5", "05", IF(AND(RIGHT(C12,1)>="A",
(RIGHT(C12,1))<="Z"), "02", IF(RIGHT(C12,1)="", "", "Something
Else")))))))







ssciarrino said:
Ok Here is my nested IF I tabbed it out for better reading.

Basically if the last digit of C12 ends in a value, C13 returns the
equivalent

So if C12 ends with

1 then 00 shows on C13
2 then 02 shows on C13
3 then 03 shows on C13
4 then 19 shows on C13
5 then 05 shows on C13
A-Z then 02 shows on C13
[blank] should show [blank]

but blank shows '02'

Here is the formula

=IF(RIGHT(C12,1)="1",TEXT(0,"00"),
IF(RIGHT(C12,1)="2",TEXT(0,"02"),
IF(RIGHT(C12,1)="3",TEXT(0,"03"),
IF(RIGHT(C12,1)="4",TEXT(0,"19"),
IF(RIGHT(C12,1)="5",TEXT(0,"05"),
IF(OR(RIGHT(C12,1)>="A",(RIGHT(C12,1))<="Z"),TEXT(0,"02"),
IF(RIGHT(C12,1)="",TEXT(0,""))))))))

thanks for the help
Click to expand...
Click to expand...
 
Change OR to AND. However, if C12 ends with a number other than 1, 2, 3, 4,
or 5 the formula will return FALSE.

Does this variation give the results you want?

=IF(RIGHT(C12,1)="1","00",
IF(RIGHT(C12,1)="2","02",
IF(RIGHT(C12,1)="3","03",
IF(RIGHT(C12,1)="4","19",
IF(RIGHT(C12,1)="5","05",
IF(ISBLANK(C12),"","02"))))))

Hope this helps,

Hutch
 
Hutch,

Thanks! Yes it does, need to think outside the box like that so hooked up
Change OR to AND. However, if C12 ends with a number other than 1, 2, 3, 4,
or 5 the formula will return FALSE.

Does this variation give the results you want?

=IF(RIGHT(C12,1)="1","00",
IF(RIGHT(C12,1)="2","02",
IF(RIGHT(C12,1)="3","03",
IF(RIGHT(C12,1)="4","19",
IF(RIGHT(C12,1)="5","05",
IF(ISBLANK(C12),"","02"))))))

Hope this helps,

Hutch

ssciarrino said:
Ok Here is my nested IF I tabbed it out for better reading.

Basically if the last digit of C12 ends in a value, C13 returns the equivalent

So if C12 ends with

1 then 00 shows on C13
2 then 02 shows on C13
3 then 03 shows on C13
4 then 19 shows on C13
5 then 05 shows on C13
A-Z then 02 shows on C13
[blank] should show [blank]

but blank shows '02'

Here is the formula

=IF(RIGHT(C12,1)="1",TEXT(0,"00"),
IF(RIGHT(C12,1)="2",TEXT(0,"02"),
IF(RIGHT(C12,1)="3",TEXT(0,"03"),
IF(RIGHT(C12,1)="4",TEXT(0,"19"),
IF(RIGHT(C12,1)="5",TEXT(0,"05"),
IF(OR(RIGHT(C12,1)>="A",(RIGHT(C12,1))<="Z"),TEXT(0,"02"),
IF(RIGHT(C12,1)="",TEXT(0,""))))))))

thanks for the help
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can I use a between clause or in clause on an IF statement 2
If statement(rookies) 3
Help with a nested IF statement 6
Adding a row with the previous one if.... 1
A simple if <> nn then Msgbox "No Match" 5
Problem with code 5
More on Combined IF Function 2
#DIV/0! Error......why is it ignoring the ' Otherwise "" ' ? 3

Back
Top