Making a discount matrix in Excel...

  • Thread starter Thread starter Wolfie
  • Start date Start date
W

Wolfie

I need to make an automatic discount matrix whereby any calls made on
the phone are discounted for each portion of their amount. For example,
a phone user had made £132.45, and the first 19.99 (1)of call charges
had a discount of 6%, then the next 14.99 (2) of calls after that had a
discount of 8%, the next 14.99 (3) after that had a discount of 12% and
any call charges remaining after that had a discount of 16% (4) this
would make a complete discount of £17.39. As you can see, this is done
for four stages only, 1 of £19.99 and 3 sets of £14.99.
I need to be able to put a list of call charges in column A and have
this all worked out to what their discount would be in another column.
However, call charges can be anywhere from 0.00 to a few hundred pounds
and I'd need the calculation to realise that, say, after the first
19.99 was calculated and if there was only 5.20 remaining,it needs to
do the calculation on the 19.99, then the 5.20 and not pass over a
remaining minus number to the next recalculation.

How can this be done?

Any help very appreciated!!



Wolfie
xx
 
One way to do this is :

Enter the following into cell B5 (output cell)

=IF(A5>SUM(B1:B3),(A5-SUM(B1:B3))*C4+B3*C3+B2*C2+B1*C1,IF(A5>SUM(B1:B2),(A5-SUM(B1:B2))*C3+B2*C2+B1*C1,IF(A5>B1,(A5-B1)*C2+B1*C1,A5*C1)))

The input cell is A5
The first band limit is B1 with it's rate in C1
the second in B2 and C2
the third in B3 and C3
and the final rate in C4

This allows you to change the band limits and the discount rates.
If this is to be applied to all other rows the limit & rate cell
references should be fixed i.e. SUM(B1:B3) >>> SUM($B$1:$B$3)

Hope this helps

Mike
 
Thankyou!!!!!

I hadn't realised until i read over my post that I'd meant to say it
was in four stages where the first is up to 19.99, two sets of 14.99
blocks, then just what's left over.

Would this make a difference to the formula?

Much appreciated!!!!

Wolfie
xx
 
I gathered that from your description, hence

Band 1 £0 to £19.99 @ 6% (B1=19.99, C1=6%)
Band 2 £20 to £34.99 @ 8% (B2=14.99, C2=8%)
Band 3 £35 to £49.99 @ 12% (B3=14.99, C3=12%)
Band 4 £50 and above @ 16% (B4=blank, C4=16%)


The formula would only need changing if you wanted to change the band
limit to the actual limit instead of the step.

Mike
 
Back
Top