|
Caculating a "staircase" discount
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 |
| All times are GMT +1. The time now is 07:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com