Complicated formula help

Hello, I want to create a formula that changes the unit price to half price when the quanitity goes above a certain amount - ie.

Columns [Quantity], [Unitprice], [Total] - I want to charge the normal unit price for up to a quanitty of 24, when the quantity is over 24, then I want the quantity that is over and above 24 to have the unit price as half price.

For example: [Quantity] is 12, [Unitprice] is $10.00, [Total] = $120.00, if [Quantity] is 26, [Unitprice] for the first 24 is $10.00, then for the extra 2 is only $5 each, so [Total] should be $250.00.

Appreciate any help from anyone on this tricky formula, THANKS!!!!

Find more posts tagged with

Comments

Robert Casper

The formula is possible but it would be long and convoluted. What I would recommend is adding three new columns called [Regular Quantity], [Discount Quantity], and [Discount Rate]

1) [Regular Quantity]1=MIN(24,[Quantity]1) /* so if it's 9 it's 9 but if it's 40 it will be 24 */

2) [Discount Quantity]1 =MAX(0, 24-[Quantity]1) /* so if it's 9 it's 0 but if it's 40 it will be 16 */

3) [Discount Rate]1=0.5

[Total]1=[Regular Quantity]1*[Unitprice]1+[Discount Quantity]1*[Unitprice]1*[Discount Rate]1

I haven't tested this so there may be a tweak I've missed. Good luck!

Rob Hagan

Hi LouSnz,

I reckon that one valid solution is (to be put into the Total column in row 1):

=(MIN(Quantity1, 24) * UnitPrice1) + (MAX(Quantity1 - 24, 0) * UnitPrice1 / 2)

The first part is the minimum of the quantity and 24. This will evaluate to 0 for a quantity of 0, 10 for a quantity of 10, 24 for a quantity of 24, but 24 for any quantity above 24. This is then mutiplied by the full unit price to give you the price component for the first part.

The second part is "subtract 24 from the quantity and if the result is less than zero then make it zero" (the MAX function is used to do this). This is then multiplied by half the unit price to give you the price component for the second part.

The first part and second part are then added together to give you the result that you desire.

If the quantity is negative, then the full unit price is used. How did you want to handle such a situation, or can't it occur in your data?

I hope that this helps.

Cheers,

Rob.

Rob Hagan

Ahh, Robert, simultaneous postings and identical solutions. Reminds me of that song "Duelling Banjos".