Formula needed for IF function
I need a formula to say if a certain cell's number is less than 1, then another cell equals 135, and if that same cell's range 12, then another cell equals 225, and so on. My ranges are:
<1 = $135
12 = $225
29 = $90
1019 = $72
2029 = $67.50
3049 = $64
5074 = $60
anything greater than 75 = RFQ
It seems I can only do one number at a time, not the range of numbers.Thank you!
Best Answer

To do this, you'll want to create a nested IF statement, using AND to create your ranges in each IF statement. In my example below, I'm using [Column Name] to represent where your numbers/range would be input. You'll need to swap this out with your own column name.
I'm also presuming you're pasting this into a Text/Number column where the format is set to be $ on the column, so you don't need to add in the $ symbol to your output.
Your first statement is easy, since there's only one criteria (less than 1).
=IF([Column Name]@row <1, 135,
Then with your second statement, you actually just need to say if it's this OR that, then 225:
IF(OR([Column Name]@row =1, [Column Name]@row =2), 225
Now we get into the ranges, so you'll have to use AND instead of OR:
IF(AND([Column Name]@row >2, [Column Name]@row <=9), 90
So, put together all of your instructions and you'll have a final formula as follows:
=IF([Column Name]@row < 1, 135, IF(OR([Column Name]@row = 1, [Column Name]@row = 2), 225, IF(AND([Column Name]@row > 2, [Column Name]@row <= 9), 90, IF(AND([Column Name]@row >= 10, [Column Name]@row <= 19), 72, IF(AND([Column Name]@row >= 20, [Column Name]@row <= 29), 67.5, IF(AND([Column Name]@row >= 30, [Column Name]@row <= 49), 64, IF(AND([Column Name]@row >= 50, [Column Name]@row <= 74), 60, "RFQ")))))))
Let me know if this works for you, or if I can explain anything further.
Cheers,
Genevieve
Answers

To do this, you'll want to create a nested IF statement, using AND to create your ranges in each IF statement. In my example below, I'm using [Column Name] to represent where your numbers/range would be input. You'll need to swap this out with your own column name.
I'm also presuming you're pasting this into a Text/Number column where the format is set to be $ on the column, so you don't need to add in the $ symbol to your output.
Your first statement is easy, since there's only one criteria (less than 1).
=IF([Column Name]@row <1, 135,
Then with your second statement, you actually just need to say if it's this OR that, then 225:
IF(OR([Column Name]@row =1, [Column Name]@row =2), 225
Now we get into the ranges, so you'll have to use AND instead of OR:
IF(AND([Column Name]@row >2, [Column Name]@row <=9), 90
So, put together all of your instructions and you'll have a final formula as follows:
=IF([Column Name]@row < 1, 135, IF(OR([Column Name]@row = 1, [Column Name]@row = 2), 225, IF(AND([Column Name]@row > 2, [Column Name]@row <= 9), 90, IF(AND([Column Name]@row >= 10, [Column Name]@row <= 19), 72, IF(AND([Column Name]@row >= 20, [Column Name]@row <= 29), 67.5, IF(AND([Column Name]@row >= 30, [Column Name]@row <= 49), 64, IF(AND([Column Name]@row >= 50, [Column Name]@row <= 74), 60, "RFQ")))))))
Let me know if this works for you, or if I can explain anything further.
Cheers,
Genevieve

Thanks, Genevieve!
This formula worked! I just changed the 12 range to be an AND statement, since I wanted it to capture between 1 and 2 (including decimals). I also changed each ending range to be .99. so that if I had something that was for example 9.5, it would have a place to live. Thank you again!!
Shannon

Hi Shannon,
Thanks for the update!! Ah, that makes sense  I'm glad it worked for you 😊
Help Article Resources
Categories
Check out the Formula Handbook template!