Conditional formula

[atmicmu]

I need some help

(don't worry about the dutch words, not important for the question)

So I'm making a sheet that can keep my income and costs every month and calculate my taxes etc automaticly.
Fot taxes and other stuff in Belgium we use a system based on net income according to the following table;

[TABLE="class: table table-striped table-bordered, width: 837"]
<tbody style="box-sizing: border-box;">[TR]
[TD="class: left, bgcolor: #F9F9F9"]Belastingschijf[/TD]
[TD="class: middle, bgcolor: #F9F9F9"]Inkomensschijf[/TD]
[TD="class: right, bgcolor: #F9F9F9"]Tarief belasting[/TD]
[/TR]
[TR]
[TD]Schijf 1[/TD]
[TD]€ 0 tot €12 990[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD="bgcolor: #F9F9F9"]Schijf 2[/TD]
[TD="bgcolor: #F9F9F9"]€ 12 990,01 t/m € 22 290[/TD]
[TD="bgcolor: #F9F9F9"]40%[/TD]
[/TR]
[TR]
[TD]Schijf 3[/TD]
[TD]€ 22 290,01 t/m € 39 660[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD="bgcolor: #F9F9F9"]Schijf 4[/TD]
[TD="bgcolor: #F9F9F9"]Meer dan 39 660,01 euro[/TD]
[TD="bgcolor: #F9F9F9"]50%[/TD]
[/TR]
</tbody>[/TABLE]

So, what I can't find is how to formulate in 1 cell that the % tax has to be according to this table?

Thanks for helping!

 

[wideboydixon]

Not sure how this converts to Dutch format formula but here's what I'd do:

Book1
	A	B	C
1	Amount	Tax %	Tax Value
2	€ 10,000.00	25%	€ 2,500.00
3	€ 12,990.00	25%	€ 3,247.50
4	€ 12,990.01	40%	€ 5,196.00
5	€ 15,000.00	40%	€ 6,000.00
6	€ 20,000.00	40%	€ 8,000.00
7	€ 25,000.00	45%	€ 11,250.00
8	€ 30,000.00	45%	€ 13,500.00
9	€ 40,000.00	50%	€ 20,000.00
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE)
C2	=$A2*$B2

Maybe it's:

=VLOOKUP($A2,{0\0.25;12990,01\0.4;22290,01\0.45;39660,01\0.5},2,TRUE)

WBD

 

[atmicmu]

Thank you WBD!

I think I understand where you are going with this. What I need is the following;

When my total income minus costs = X
And the calculation you make is Y

How do i: Create 1 cell that automaticly looks at X and decides what % to use (Y) to give me a total that is (gros-taxes=net)

 

[wideboydixon]

I guess that would be column D:

Book1
	A	B	C	D
1	Amount	Tax %	Tax Value	Net
2	€ 10,000.00	25%	€ 2,500.00	€ 7,500.00
3	€ 12,990.00	25%	€ 3,247.50	€ 9,742.50
4	€ 12,990.01	40%	€ 5,196.00	€ 7,794.01
5	€ 15,000.00	40%	€ 6,000.00	€ 9,000.00
6	€ 20,000.00	40%	€ 8,000.00	€ 12,000.00
7	€ 25,000.00	45%	€ 11,250.00	€ 13,750.00
8	€ 30,000.00	45%	€ 13,500.00	€ 16,500.00
9	€ 40,000.00	50%	€ 20,000.00	€ 20,000.00
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE)
C2	=$A2*$B2
D2	=$A2*(1-VLOOKUP($A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE))

WBD