Skip to main content

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

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

)))))

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

)))))

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

)))))

Reply


Terms & Conditions