Excel-97 (workaround for VBA or Visual Basic)

[dw85745]

M$ killed the use of VBA and Visual Basic some time back for older versions of Excel.
I normally handle this by creating a VB program and then doing everything in Excel from VB,
I have a situation with multiple interrelated worksheets that I don't want to recreate in VB.
OF INTEREST is a tax table I need to reference, which calculates the amount of tax based on the final value in one cell.
Currently, I'm using the formula which just address the first two tax levels which reside in rows.

 =IF(C37 <= 0, 0, IF(AND(C37>$D$54, C37 < $D$55),((C37-$D$54)*$C$54)+$B$54,((C37-$D$55)*$C$55)+$B$55))

I need to expand this for other tax levels (rows), which would require a loop.

Any workaround or solution appreciated.

 

[Rijnsent]

SUMPRODUCT is probably your answer... It's hard to replicate your example as you didn't provide the data, but this e.g. works:

Say you have 4 backets, add one column for the "total tax in previous brackets".

start (cell A1)	end	perc	max_prev
-	6.000	10%​	0
6.000	12.000	20%​	600 =D2+C2*(B2-A2)
12.000	25.000	30%​	1.800 =D3+C3*(B3-A3)
25.000	100.000	40%​	5.700 =D4+C4*(B4-A4)

Let's put the example income data in F2:F12:

amount (cell F1)	max_prev	perc	in_bracket	calc
-1.000,00	0 =SUMPRODUCT(($A$2:$A$5<=F2)*($B$2:$B$5>F2)*($D$2:$D$5))	0% =SUMPRODUCT(($A$2:$A$5<=F2)*($B$2:$B$5>F2)*($C$2:$C$5))​	- =MAX(F2-SUMPRODUCT(($A$2:$A$5<=F2)*($B$2:$B$5>F2)*($A$2:$A$5)),0)	- =G2+I2*H2
2.000,00	0	10%​	2.000,00	200,00
5.000,00	0	10%​	5.000,00	500,00
8.000,00	600	20%​	2.000,00	1.000,00
11.000,00	600	20%​	5.000,00	1.600,00
14.000,00	1.800	30%​	2.000,00	2.400,00
17.000,00	1.800	30%​	5.000,00	3.300,00
20.000,00	1.800	30%​	8.000,00	4.200,00
23.000,00	1.800	30%​	11.000,00	5.100,00
26.000,00	5.700	40%​	1.000,00	6.100,00
29.000,00	5.700	40%​	4.000,00	7.300,00

Basically you can use the SUMPRODUCT as a kind of filter/multiply.

Cheers