Help with IF Formulas and Calculations

[Erika_M]

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

[Kamille_Parks]

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
)))))

[Justin_Barrett]

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
)))))