Struggling with making Excel do the math on what I hope is a comparatively simple problem. I want to calculate the result after applying discounts that work on successive slices of a price. So in Cell G1, I place the price. The customer is entitled to a discount depending on the price. Up to 5000 there is no discount, between 5000 and 10000 a 5% discount on the excess, between 10000 and 20000, 10% on the excess and above 20000, 15% on the excess.
So if the price were 8500 the discounted price would be 5000 +(3500*0.95) = 8325.
If the price were 22000 then the discounted price would be 5000 + (5000*0.95) + (10000*0.90) +(2000*0.85) = 20450
Ideally I would like to set up a table so that the threshholds are not "hard-coded", i.e. with the thresholds 0, 5000, 10000, 20000 in A1:A4 and the discounts (or discount multipliers) 1, 0.95, 0.90, 0.85 in B1:B4. Tried using SUMIF but can't seem to make it work.
So if the price were 8500 the discounted price would be 5000 +(3500*0.95) = 8325.
If the price were 22000 then the discounted price would be 5000 + (5000*0.95) + (10000*0.90) +(2000*0.85) = 20450
Ideally I would like to set up a table so that the threshholds are not "hard-coded", i.e. with the thresholds 0, 5000, 10000, 20000 in A1:A4 and the discounts (or discount multipliers) 1, 0.95, 0.90, 0.85 in B1:B4. Tried using SUMIF but can't seem to make it work.
