Forum Discussion
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...
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})
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
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})- 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
