Calculate deduct that increases in levels based on total amount in Google Sheets

Question

Im setting up a spreadsheet to calculate a variable rebate/deduct that changes based on the total value spend.

From 0 to 980, there is zero deduct.
From 980 to 1625 there is a 50% deduct
From 1625 to 3520 there is a 75% deduct
From 3520 to 19078 there is a 85% deduct
From 19078 to 23428 there is 100% deduct

What I want to do is to be able to add items with varying prices that will automatically calculate the deduct based on the total amount payed to this day, but it needs to calculate the deduct individually on each item, as the deduct changes based on amount spend from time to time.

I have the sheet working as long as I input both the price and the deduct. What I want is for the deduct to be automatically calculated based on the above rules so that I only have to enter the price, making it possible for me to calculate my upcoming payments and deducts in advance.

Ie. the first item costs 666.95 and would show up like this:

  PRICE  |  DEDUCT  |  PRICE AFTER DEDUCT
-------------------------------------------
  666.95 |  0       |  666.95
  975    |  335.25  |  639.75

TOTAL PAYED  |  TOTAL DEDUCT
-----------------------------
1306.70      |  335.25

Please note that the deduct percentage applys on an item level and only from 980 and upwards. That means that the first item gets no deduct and the second item gets a 50% deduct but ONLY from when 980 is reached and only applies to whatever is leftover after reaching 980. This means that on item #2 there is a deduct of 335,25.

TLDR; How do I calculate the deduct automatically, taking into account that anything below 980 is not eligible for deduct and that the deduct amount changes based on total spending?

Calculate deduct that increases in levels based on total amount in Google Sheets

Answers (1)

Related Questions