Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Help with IF Formulas and Calculations

Topic Labels: Formulas
495 2
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

Hi there,

I’m not too familiar with formulas in general and haven’t succeeded after reading many posts. I need to have a pre-filled selling price column under certain circumstances. I’m not sure if it’s possible to have many ranges as I’m requesting. The multiplying doesn’t need to happen in the same column, I just need to get the “x multiply by” number in a column first.

Here are the different circumstances in a table (with examples in the last 2 columns), and written out how I think they should be for a formula.

If PC is between these two numbers, then multiply by X. IF(pc<=2,“3.5”,

IF(pc>=2.01,“3”,
IF(pc<=5,“3”

IF(pc>=5.01,“2.5”,
IF(pc<=7,“2.5”

IF(pc>=7.01,“2”,
IF(pc<=10,“2”

IF(pc>=10.01,“1.75”,
IF(pc<=40,“1.75”

Any help is much appreciated!
Erika

2 Replies 2  16 - Uranus

Your formula would be something like:

``````{Example PC} *
IF({PC}<=2, 3.5,
IF(AND({PC}>=2.01,{PC}<=5), 3,
IF(AND({PC}>=5.01,{PC}<=7), 2.5,
IF(AND({PC}>=7.01,{PC}<=10), 2,
IF(AND({PC}>=10.01,{PC}<=40), 1.75
)))))``````  18 - Pluto

Because the values are being checked in ascending order, that could be simplified a bit to only check to see if it’s under the next threshold in the sequence:

``````{Example PC} *
IF(PC<=2, 3.5,
IF(PC<=5, 3,
IF(PC<=7, 2.5,
IF(PC<=10, 2,
IF(PC<=40, 1.75
)))))
`````` 