Stepped discounts equation in excel?

Question

Hi,

Excel question regarding working out stepped discounts.

I need the equation for excel to work out the following.

If there was a discount scheme where up to the first £50 was discounted at 10% and anything over was discounted at 20%

I.E.:
An item sells for £36 it is subject to 10% discount which is £3.60 (I have this part worked out)

An item sells for £65 then the first £50 is subject to 10% and the remaining £15 is subject to 20% (this is the part i am struggling with)

So what i am after is help with an excel equation that will look at an adjacent cell and automatically work out the above.

Any help greatly appreciated.

Answer 1

I assume the price is in A1.
In A2 you put:
=if(A1>50,(A1-50)*.2+45,A1*.1)
The logic: If the amount is less than 50, he gets 10% of whatever the price is. If it is more than 50, he gets 20% off the price above 50 + 45 which is the first 50 less 10%.

Answer 2

Let's say you put the price in column A (let's focus on cell A1 for a moment).

Then calculate these in cells B1 and C1:
B1 =MIN(A1,50) (this is the amount to discount by 10%)
C1 =A1-B1 (this is the amount to discount by 20%)

D1 =10%*B1+20%*C1 (this is the effective discount)

You can combine all of this into one complicated formula if you really need to...

Answer 3

A1 is the full price
B1 is the discount:

=.1*A1+.1*MAX(0,A1-50)

Answer 4

Assume price in cell D6
Formula to go in cell E6 will be :-
=IF(D6<=50,0.1* D6,0.2*(D6-50)+50*0.1)
I have tested this and it would appear to be what you are looking for. Hope so!

Answer 5

below the numbers positioned this: =sum( and from there on a similar time as conserving the ctrl key choose the numbers you like it to function, and once you have choosen each and all of the numbers that could desire to be further then positioned ) and hit the enter key and the entire will seem.

Answer 6

=IF(D8>=200,"15%",IF(D8>=100,"10%"," "))

wen u enter it into excel it will say in the formula bar which vaule is which