Hi,

Set up your worksheet as follows (Range B2:C7):

Income slab Rate

- 0%
4,000,000.00 4%
8,000,000.00 8%
12,000,000.00


This implies that for the first 4,000,000 there is no discount, for the next
4,000,000 there is a 4% discount etc.

Now in cell C10, enter the amount on which you want to calculate the discount.

In cell C12, array enter the following array formula (Ctrl+Shift+Enter)

SUM(IF($C$10<B4:B8,0,IF(B5:B9-B4:B8$C$10,$C$10-B4:B8,IF(B5:B9-B4:B80,IF(B5:B9-B4:B8$C$10-B4:B8,$C$10-B4:B8,B5:B9-B4:B8),$C$10-B4:B8)))*(C4:C8))

Regards,

Ashish Mathur


"Thomas Refsdal" wrote:

Hi.

I'm trying to calculate i discount that is applied in steps (in Norwegian
this is called "staricase" rebate). If total sales are between 0 and 4
million no discount is applied, between 4 and 8 million 4% is applied on
sales above 4 million, if total sales are between 8 and 12 million 4% is
applied on the sales between 4 and 8 million and 6 % on sales above 8
million, etc.

The structure can be illustrated like this
From To Discount
3 999 999 0,00
4 000 00 0 7 999 999 0,04
8 000 000 11 999 999 0,06
12 000 000 15 999 999 0,08
16 000 000 0,10

I could make a nested IF-statement looking someting like
'=-1*IF(N$8<k!$C$4;N$8*k!$D$4;IF(N$8<k!$C$5;k!$C$4*k! $D$4+(N$8-(k!$C$4))*k!$
D$5;IF(N$8<k!$C$6;k!$C$4*k!$D$4+(k!$C$5-k!$C$4)*k!$D$5+(N$8-(k!$C$5))*k!$D$6
;k!$C$4*k!$D$4+(k!$C$5-k!$C$4)*k!$D$5+(k!$C$6-k!$C$5)*k!$D$6+(N$8-k!$C$6)*k!
$D$7)))', but there must be a more simple way of calculating this kind of
discount.



Rgds

Thomas, Norway