If Functions

I have a demand table 6,000 through 20,000 (X-Axis) and Order Quantities 6,000 through 20,000 (Y-Axis). I am trying to develop a formula that will generate the revenue (Includes Salvage price 20.00) minus the cost to order a given number of units. I started by using a multiple Ifs function but was unable to accurately state the argument.


I basically want to say if order quantity is <=10K * 93.00, if order quantity is <=10K * 93.00 anything over 10K and less than 13K is multiplied by 79.05 + anything over 13K is 69.75. Any of the Order Quantities not sold (Order Quantity – Demand) will be liquidated/ salvaged for 20.00.Thank you for your help and guidance.

=IF(D13<=10000,B6*D13)),iF(D18>10000,(D18-10000)*(B6*0.85), IF(D21>13000,(D21-13000)*(B6*0.75))

Sales Price $125.00
Salvage Sales Price $20.00
Variable Cost Up to 10K $93.00 (Original Cost)
Variable Cost 10K - 13K $79.05 (15% off original cost)
Variable Cost Over 13K $69.75 (25% off original cost)
*The cost to the retailer for an order of 12,000 units will be 10,000*$93 + 2,000*(0.85*$93).

Attached is the spreadsheet. The cells highlighted in yellow must be populated with the formula. The initial formula has a fixed unit cost of 80, whereas now the unit cost varies Variable Cost Up to 10K $93.00 (Original Cost),Variable Cost (10K - 13K) $79.05 (15% off original cost), Variable Cost Over (13K) $69.75 (25% off original cost).

Hi Kontra, it took me a moment to get my head around what you were trying to do, but I'm pretty sure I have it now.
Just want to confirm 1 thing atm. If the Demand is 20,000 and Order Qty is 6,000 (L13) then you want the price of 20,000 units worked out at the 6,000 unit price..not at the higher qty rates.

If the above assumption was right, then I believe this should work for you.

Edit: just revoked my last edit....was thinking the wrong way around and it was right all along.