Calculation of fees in different slab

[Harikrushna]

Please provide me excel working or VB for below mention Condition & Logic.


Input taken
1- Existing
2 - After Increase

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247"]Base fee Up to 100000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247"]For every additional 10000 or part of it[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] 100,001 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148"] 500,000 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247, align: right"]400[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] 500,001 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148"] 5,000,000 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247, align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] 5,000,001 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148"] 10,000,000 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247, align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] 10,000,001 [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 125"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148"] Unlimited [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 247"]
<tbody>[TR]
[TD="width: 247, align: right"]75[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


For Eg.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] If existing [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148"] 400,000 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] After Increased [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148"] 600,000 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] then fee is [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]500000-400000=100000[/TD]
[TD]100000/10000=10[/TD]
[TD]10*400=4000[/TD]
[/TR]
[TR]
[TD]100000[/TD]
[TD]100000/10000=10[/TD]
[TD]10*300=3000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total Fees is[/TD]
[TD]4000+3000+5000(Base Fee)=12000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] If existing [/TD]
[TD]600000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"] After Increased [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]800000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200000[/TD]
[TD]200000/10000=20[/TD]
[TD]20*300=6000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total Fees is[/TD]
[TD]6000+5000=11000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Harikrushna]

Its urgent. please anyone have set of formulas or VB macros or coding for the same?

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]First 100K[/td][td]

5,000​

[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

100,000​

[/td][td]

400​

[/td][td]

400​

[/td][td]C3: =B3-B2[/td][/tr]

[tr][td]

4​

[/td][td]

500,000​

[/td][td]

300​

[/td][td]

(100)​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

5,000,000​

[/td][td]

100​

[/td][td]

(200)​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

10,000,000​

[/td][td]

75​

[/td][td]

(25)​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

100,000​

[/td][td]

5,000​

[/td][td][/td][td]B9: =$B$1 + SUMPRODUCT((A9 > $A$3:$A$6) * CEILING((A9 - $A$3:$A$6)/10000, 1) * $C$3:$C$6)[/td][/tr]

[tr][td]

10​

[/td][td]

100,001​

[/td][td]

5,400​

[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

200,000​

[/td][td]

9,000​

[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

200,001​

[/td][td]

9,400​

[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

300,000​

[/td][td]

13,000​

[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

300,001​

[/td][td]

13,400​

[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

400,000​

[/td][td]

17,000​

[/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]

400,001​

[/td][td]

17,400​

[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

500,000​

[/td][td]

21,000​

[/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

500,001​

[/td][td]

21,300​

[/td][td][/td][td][/td][/tr]
[/table]

 

[Harikrushna]

Its working but does not consider existiong amount. pls refer eg. 2 given in the #1

 

[shg]

If you want the difference in fees between two levels, calculate both and subtract.