Forum Discussion

MarkintheHV's avatar
MarkintheHV
Copper Contributor
May 06, 2021

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?

excel

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 06, 2021

    MarkintheHV 

    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})
    • MarkintheHV's avatar
      MarkintheHV
      Copper Contributor
      May 06, 2021
      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
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 11, 2021

        MarkintheHV 

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

Resources