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