ExcelBanter - Problem with my Nested IF, the Else output is incorrect

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Problem with my Nested IF, the Else output is incorrect (https://www.excelbanter.com/excel-programming/388867-problem-my-nested-if-else-output-incorrect.html)

ssciarrino

Problem with my Nested IF, the Else output is incorrect

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

JLGWhiz

Problem with my Nested IF, the Else output is incorrect

<="Z" gives "02" Then "" is <="Z"

"ssciarrino" wrote:

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

ssciarrino

Problem with my Nested IF, the Else output is incorrect

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" wrote:

<="Z" gives "02" Then "" is <="Z"

"ssciarrino" wrote:

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

JE McGimpsey

Problem with my Nested IF, the Else output is incorrect

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")))))))

In article ,
ssciarrino wrote:

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

JE McGimpsey

Problem with my Nested IF, the Else output is incorrect

And of course, the last RIGHT(C12,1)="" is superfluous - should be

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

In article ,
JE McGimpsey wrote:

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")))))))

In article ,
ssciarrino wrote:

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

Tom Hutchins

Problem with my Nested IF, the Else output is incorrect

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" wrote:

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

ssciarrino

Problem with my Nested IF, the Else output is incorrect

Hutch,

Thanks! Yes it does, need to think outside the box like that so hooked up
on the letters !

"Tom Hutchins" wrote:

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" wrote:

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

All times are GMT +1. The time now is 01:32 PM.