Formula needed for IF function

Shannon Scarbrough

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 1-2, then another cell equals 225, and so on. My ranges are:

<1 = $135

1-2 = $225

2-9 = $90

10-19 = $72

20-29 = $67.50

30-49 = $64

50-74 = $60

anything greater than 75 = RFQ

It seems I can only do one number at a time, not the range of numbers.Thank you!

Genevieve P.

Hi @Shannon Scarbrough

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

Genevieve P.

Hi @Shannon Scarbrough

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

Shannon Scarbrough

Thanks, Genevieve!

This formula worked! I just changed the 1-2 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

Genevieve P.

Hi Shannon,

Thanks for the update!! Ah, that makes sense - I'm glad it worked for you 😊