Calculate Land Tax in Tasmania - seeking Excel formula

[matwright06]

Hi guys,

I posted asking for help for a stamp duty calculator recently and received some awesome solutions from the users on here. I tried tweaking the formula for this exercise but couldn't get my head around it. This time I'm trying to calculate land tax due for properties in Tasmania using the scale below.

I have my property prices (Total Land Value) in Column B starting at B2.

Any suggestions would be fantastic. Thank you!

Mat

Total Land Value​

<tbody>
[TD="width: 312"]

Current Tax Scale​

[/TD]

[TD="width: 246"]$0 – $24 999[/TD]
[TD="width: 312"]Nil[/TD]

[TD="width: 246"]$25 000 - $349 999[/TD]
[TD="width: 312"]$50 plus 0.55% of value above $25 000[/TD]

[TD="width: 246"]$350 000 and above[/TD]
[TD="width: 312"]$1 837.50 plus 1.5% of value above $350 000[/TD]

</tbody>

 

[Logit]

.
Too many formulas to post here and explain. Take a look at this file :

https://www.amazon.com/clouddrive/s...DCBwHtJ64DsKFS3rDj?ref_=cd_ph_share_link_copy

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Total Land Value	500,000.00
2	Tax	5,212.50
3	or	5,212.50
4	or	5,212.50
5	or	5,212.50
6
7	Brackets	Increment	Rate	Rate_Diff
8	0	0	0	0
9	25,000	50.00	0.55%	0.55%
10	350,000	1,837.50	2.25%	1.70%
11
12	Try the alternative that you prefer.
13	The above include some of the suggestions made previously.
14	To help visualize and understand a formula,
15	you can try using Excel's Formula Evaluate ( Formulas | Formula Auditing).
16
17	T10_1701c	2aaa
2aaa

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(B1>A8:A10),B1-A8:A10,D8:D10)+(B1>=A9)*B9
B3	=VLOOKUP(B1,A8:D10,2,1)+VLOOKUP(B1,A8:D10,3,1)*(B1-VLOOKUP(B1,A8:D10,1,1))
B4	=LOOKUP(B1,A8:A10,B8:B10)+LOOKUP(B1,A8:A10,C8:C10)*(B1-LOOKUP(B1,A8:A10))
B5	=(B1>=A9)*B9+(B1>=A9)*(B1-A9)*C9+(B1>=A10)*(B1-A10)*(C10-C9)
B10	=(A10-A9)*C9+B9
D9	=C9-N(C7)
D10	=C10-N(C9)