New Contributor

# IF statement issue appears to be only reading 2 digits

I need to match criteria on the column U, and have written a Boolean if statement (see below)to match on the desired ranges and place the appropriate value in column X which would be 1-8.  As you can see below, the formula works fine on rows 1,2,3 and when it hits row 4, the formula breaks, then adds the value of 1 on the remaining columns which should be 6,7,8.  I suspect that the if statement for some reason is only looking at the first 2 digits of column U because row 4-9 is where the 3 digit ranges start, and I get a failure due to the formula being =>96 and <128, which I suspect Excel is reading =>96 and <12 which generates the value of false and if it is indeed only reading the first 2 digits that would explain the values of 1 for 160, 192, 224, 255.

=

IF(AND(U2 >= "0",U2 < "32"),1,

IF(AND(U2 >= "32",U2 < "64"),2,

IF(AND(U2 >= "64",U2 < "96"),3,

IF(AND(U2 >= "96",U2 < "128"),4,

IF(AND(U2 >= "128",U2 < "160"),5,

IF(AND(U2 >= "160",U2 < "192"),6,

IF(AND(U2 >= "192",U2 < "224"),7,

IF(AND(U2 >= "224",U2 < "256"),8

))))))))

Test data is attached.

Any ideas as to how to solve this?

5 Replies

# Re: IF statement issue appears to be only reading 2 digits

The problem is in the workbook, not in the picture,

# Re: IF statement issue appears to be only reading 2 digits

I guess you work with numbers, if so correct one will be

``````IF(AND(U2 >= 0,U2 < 32),1,
IF(AND(U2 >= 32,U2 < 64),2,
IF(AND(U2 >= 64,U2 < 96),3,
IF(AND(U2 >= 96,U2 < 128),4,
IF(AND(U2 >= 128,U2 < 160),5,
IF(AND(U2 >= 160,U2 < 192),6,
IF(AND(U2 >= 192,U2 < 224),7,
IF(AND(U2 >= 224,U2 < 256),8
))))))))
``````

or

``````=LOOKUP(U2,
{0,32,64,96,128,160,192,224,256},
{1,2,3,4,5,6,7,8,0})``````

# Re: IF statement issue appears to be only reading 2 digits

Interestingly when I remove the quotes as illustrated above, all the values return as false. I have verified the formatting of the cells and they are all formatted as number

# Re: IF statement issue appears to be only reading 2 digits

Check your numbers in column U, make sure there aren't spaces with Char(32), or Char(160).
Char(32) can be removed by the TRIM Function
Char(160) can be removed by the SUBSTITUTE Function

=TRIM(SUBSTITUTE(U2,CHAR(160),))

Cheers

# Re: IF statement issue appears to be only reading 2 digits

I see, that's since U2 is also the text, you need to convert it number to have correct results. That could be

``````=IF(AND(--U2 >= 0,--U2 < 32),1,
IF(AND(--U2 >= 32,--U2 < 64),2,
IF(AND(--U2 >= 64,--U2 < 96),3,
IF(AND(--U2 >= 96,--U2 < 128),4,
IF(AND(--U2 >= 128,--U2 < 160),5,
IF(AND(--U2 >= 160,--U2 < 192),6,
IF(AND(--U2 >= 192,--U2 < 224),7,
IF(AND(--U2 >= 224,--U2 < 256),8
))))))))``````

or

``````=LOOKUP(--U2,
{0,32,64,96,128,160,192,224,256},
{1,2,3,4,5,6,7,8,0})``````