Forum Discussion
formula with many conditions using greater than less than and equal to in spreadsheet
I am having an error with a formula that has result conditions with multiple arguments. In the equation I am trying establish the following:
if column "B" is 1 but less than 5 result in Column "C" is 2; or if column "B" is 5 but less than 10 result in Column "C" is 3; or if column "B" is 10 but less than 25 result in Column "C" is 4; but if column "B" is 25 or greater than 25 result in Column "C" is 5
I have tried, "=IF(B6>1<5,2,IF(B6>5<10,3,IF(B6>10<25,4,IF(B6>=25,5)))) but the results all returned as 5 even when it should be one of the other results instead.
Can you please help? thank you
6 Replies
Data_clerk The IFS function could be used to use no nested IF statements:
Since it stops after it finds a value that meets the condition, there's no need to use AND clauses to check if values are between specific values.
=IFS(B2<1, 0, B2<5,2,B2<10,3,B2<25,4,TRUE,5)
Just keep in mind that IFS is only available in Excel 2019 or O365.
This equation has worked as well. there seems to be multiple ways to get the same result. macrordinary
Try this one. A bit different from the traditional nested IF formula:
=(AND(B6>1,B6<5)*2+AND(B6>=5,B6<10)*3+AND(B6>=10,B6<25)*4+(B6>=25)*5)thank you, that worked; added equal sign before the "1" also to include singular quantity. thank you again
