May 06 2021 08:10 AM - edited May 06 2021 08:13 AM
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?
May 06 2021 08:23 AM
The problem is in the workbook, not in the picture,
May 06 2021 08:26 AM
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})
May 06 2021 08:38 AM
May 10 2021 01:54 PM
May 11 2021 02:56 AM
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})