Cumulative sum of invoice value upto a certain value

[zijewell]

Cumulative invoice value will be considered to calculated taxable amount basis of which to compute tax to be deducted against each invoice value. Kindly please please to come to cumulative taxable amount based on threshold limit.

Inv No Inv Amount
ALS999MAP 551799
ALS999MAP1 1563001
ALS999MAP2 419580
ALS999MAP5 210769
ALS999MAP4 1079473
als999map3 1746885
ALS999MAP6 1655173
ALS999MAP7 264749
ALS999MAP8 4780390
ALS999MAP11 1149962
ALS999MAP9 2830373
ALS999MAP12 435155


Grand Total 16687309

Tax Calculation

Slab rates Taxable amount Rate of Tax Tax Amount To be deducted
On 1st - to 1,500,000 Tk. 551,799 @ 2% = 16,554 16,554
On next 1,500,001 to 2,500,000 Tk. 2,114,800 @ 3% = 84,592 68,038
On next 2,500,001 to 10,000,000 Tk. 7,491,429 @ 4% = 374,571 289,979
On next 10,000,001 to 50,000,000 Tk. 16,687,309 @ 5% = 1,001,239 626,667
On next 50,000,000 to 100,000,000 @ 6% = -
On next 100,000,001 to 10,764,455,899 @ 7% =

 

[Special-K99]

Is this a tiered calculation?

Am no expert on tiered calculations but does this work?

=SUMPRODUCT((B1>{0,1500000,2500000,10000000,50000000,100000000,1076455899})*(B1-{0.02,0.03,0.04,0.05,0.06,0.07})*{0.02,0.03,0.04,0.05,0.06,0.07})

 

[zijewell]

=IF($P5>100000000,$P5*7%,IF($P$5>=50000001,$P5*6%,IF($P5>=10000001,$P5*5%,IF($P5>=2500001,$P5*4%,IF($P5>=1500001,$P5*3%,IF($P5>=0,$P5*2%,0))))))

Cumulative tax calculation.

Many thanks for your kind reply.

 

[Special-K99]

Actually I think my solution should have been

=SUMPRODUCT((B1>{0,1500000,2500000,10000000,50000000,100000000,1076455899})*(B1-{0,1500000,2500000,10000000,50000000,100000000,1076455899})*{0.02,0.03,0.04,0.05,0.06,0.07})