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

There may be better ways, Total sales in A22:

=(A228000000)*(A22-8000000)*0.06+(A224000000)*MIN(4000000,A22-4000000)*0.04

What happens over 12 million?

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Thomas Refsdal" wrote in message
...
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

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

Hi Thomas,

Non array way

Table A1:B6

Amount Rate
0 0% == range 0 to 3999999
4000000 4% == range 4000000 to 7999999
8000000 6% == range 8000000 to 11999999
12000000 8% == range 12000000 to 15999999
16000000 10% == range 16000000 and above

E1 = Your Sale Figure

E2 =INDEX($A$2:$B$6,MATCH($E$1,$A$2:$A$6),2) == Return to discount rate

or

E2 =E1-(E1*INDEX($A$2:$B$6,MATCH($E$1,$A$2:$A$6),2)) = Value after discount.

Sample file:
http://savefile.com/files/5464123

kk


"Thomas Refsdal" wrote in message
...
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