Conditional formula

atmicmu

New Member
Joined
Jul 16, 2018
Messages
2
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;

BelastingschijfInkomensschijfTarief belasting
Schijf 1€ 0 tot €12 99025%
Schijf 2€ 12 990,01 t/m € 22 29040%
Schijf 3€ 22 290,01 t/m € 39 66045%
Schijf 4Meer dan 39 660,01 euro50%

<tbody style="box-sizing: border-box;">
</tbody>

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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Not sure how this converts to Dutch format formula but here's what I'd do:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Amount</td><td style=";">Tax %</td><td style=";">Tax Value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">€ 10,000.00</td><td style="text-align: right;;">25%</td><td style="text-align: right;;">€ 2,500.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">€ 12,990.00</td><td style="text-align: right;;">25%</td><td style="text-align: right;;">€ 3,247.50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">€ 12,990.01</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">€ 5,196.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">€ 15,000.00</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">€ 6,000.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">€ 20,000.00</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">€ 8,000.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">€ 25,000.00</td><td style="text-align: right;;">45%</td><td style="text-align: right;;">€ 11,250.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">€ 30,000.00</td><td style="text-align: right;;">45%</td><td style="text-align: right;;">€ 13,500.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">€ 40,000.00</td><td style="text-align: right;;">50%</td><td style="text-align: right;;">€ 20,000.00</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=$A2*$B2</td></tr></tbody></table></td></tr></table><br />

Maybe it's:

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

WBD
 

atmicmu

New Member
Joined
Jul 16, 2018
Messages
2
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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
I guess that would be column D:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Amount</td><td style=";">Tax %</td><td style=";">Tax Value</td><td style=";">Net</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">€ 10,000.00</td><td style="text-align: right;;">25%</td><td style="text-align: right;;">€ 2,500.00</td><td style="text-align: right;;">€ 7,500.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">€ 12,990.00</td><td style="text-align: right;;">25%</td><td style="text-align: right;;">€ 3,247.50</td><td style="text-align: right;;">€ 9,742.50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">€ 12,990.01</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">€ 5,196.00</td><td style="text-align: right;;">€ 7,794.01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">€ 15,000.00</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">€ 6,000.00</td><td style="text-align: right;;">€ 9,000.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">€ 20,000.00</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">€ 8,000.00</td><td style="text-align: right;;">€ 12,000.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">€ 25,000.00</td><td style="text-align: right;;">45%</td><td style="text-align: right;;">€ 11,250.00</td><td style="text-align: right;;">€ 13,750.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">€ 30,000.00</td><td style="text-align: right;;">45%</td><td style="text-align: right;;">€ 13,500.00</td><td style="text-align: right;;">€ 16,500.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">€ 40,000.00</td><td style="text-align: right;;">50%</td><td style="text-align: right;;">€ 20,000.00</td><td style="text-align: right;;">€ 20,000.00</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=$A2*$B2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=$A2*(<font color="Blue">1-VLOOKUP(<font color="Red">$A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

WBD
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top