Jump to content


Photo

Running down thru a table


  • Please log in to reply
10 replies to this topic

#1 TallPaul

TallPaul

    Newbie

  • Members
  • Pip
  • 9 posts

Posted 06 March 2018 - 08:44 PM

Which function(s) would be the best and/or most efficient to use for the following?

I have a 12 row  table that I will use for payroll withholding.

 

 Single No Deductions

 

 

 

Use

492.00

567.00

9.39

567.00

641.00

12.47

641.00

715.00

16.17

715.00

789.00

20.01

789.00

863.00

24.45

863.00

937.00

28.89

937.00

1,010.00

33.27

1,010.00

1,083.00

37.65

1,083.00

1,156.00

42.03

1,156.00

1,229.00

46.41

1,229.00

1,302.00

50.79

1,302.00

1,374.00

55.11

 

If Cell C4 is greater than N3 and less than O3, then Cell D4 = P3

If not keep going down the table until the correct range is found.

 

OR is there a better way to approach his problem?

 

Thanks

 

Paul



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 07 March 2018 - 04:13 PM

Hi Paul,

 

It's a little difficult for me to see which cell is which from your post. If you could attach a workbook I can get a better look.

 

You can see how to attach workbooks to a post here.

 

From your description it sounds like you're trying to use Excel as a tool to iterate through multiple records and return a result. This isn't a typical use of Excel and would generally be done by a program or database querying tool rather than a spreadsheet.

 

That said, you can probably achieve what you need by using the IF function to check whether the previous row returned a result and continue calculating otherwise.

You can see more about the IF function in: Expert Skills Session 3 (Lesson 3-5: Use the IF logic function).

 

If you can reply and attach a workbook with your example data I should be able to put together a working example for you.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 TallPaul

TallPaul

    Newbie

  • Members
  • Pip
  • 9 posts

Posted 07 March 2018 - 08:20 PM

Sorry about the way that showed up. I tried to cut and paste from a Word document into here and that didn't work out well. I thought I had deleted it.

What I was trying to show was that by using the VLOOKUP function and going down thru a table and when it gets a match the cell that it returns can it be a calculation instead of just a value of some sort.

 

                S T A T E                                                                                            F E D E R A L

 

COLUMN1        COLUMN2        COLUMN3                                      COLUMN!        COLUMN2          COLUMN3      

  863.00                   937.00               28.89                                         1102.00          3533.000              79.40 + 12% over 1102.00

 

When VLOOKUP gets a match for 885.00 and returns 28.89, can that COLUMN3 have for example, =C5*0.0145 + C6*0.062  instead of 28.89  ??

 

Thanks

Paul

 

PS what I an defining are state and federal income tax withholding tables  The state is easy, it just returns an amount within an income range. The fed is more difficult as it has a set amount plus a percentage of the difference between the minimum range and what's left after subtracting the set amount. 



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 08 March 2018 - 11:05 AM

Hi Paul,

 

If you just want to extract a certain calculation using VLOOKUP, the simplest solution would be to create a column in your lookup table with a formula that carries out your chosen calculation and then return the value from this column with your VLOOKUP function.

The VLOOKUP function is covered in depth in: Expert Skills Session 3 (Lesson 3-22: Use a VLOOKUP function for an exact lookup).

 

If you need a greater degree of logic (ie. performing a different calculation depending on the results of your VLOOKUP function), you would probably find that the IF function serves your needs.

You can see more about the IF function in: Expert Skills Session 3 (Lesson 3-5: Use the IF logic function).

 

There's also a 'secret' undocumented EVALUATE function that lets you place formulas in cells as text and execute them. I wouldn't recommend using it, but if you're curious you can see more about it in this thread.

 

I hope this is helpful. If you could attach a workbook with an sample of your data I could provide a working example for you. You can see how to attach workbooks to a post here.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 TallPaul

TallPaul

    Newbie

  • Members
  • Pip
  • 9 posts

Posted 09 March 2018 - 01:27 PM

I am enclosing a sample work book with the problem I am working on.

The problem is to produce a monthly payroll register for a temp employee working various hours a week and gets  paid monthly with a rate of 25.50 per hour.

All the other calculations seem to be working OK except for the Fed withholding

I just can't figure an efficient way to do this using VLOOKUP.

When rates change I would like to just change the tables and not have to modify a lot of IF or IFS statements.

Thanks,

 

Paul

Attached Files



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 11 March 2018 - 06:49 PM

Hi Paul,

 

I've taken a look and your VLOOKUP function seems to be working correctly, but it looks like you're using pre-calculated tables of tax amounts instead of tax rates. I'm not entirely sure exactly how you would like this to work, but it seems that you could do this by placing the tax rates in your table, using VLOOKUP to retrieve them and then multiplying the gross by the tax rate to get the actual amount.

 

If there's a logic behind your tax calculations you should be able to model it using formulas. I'm afraid I'm not familiar with federal tax calculations, but if you can explain the formula that you are trying to create I should be able to help.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#7 TallPaul

TallPaul

    Newbie

  • Members
  • Pip
  • 9 posts

Posted 12 March 2018 - 11:13 AM

You are correct in that state and federal withholding is determined by pre-determined tax tables. as you say, the state is easy.

 

The rules for federal withholding are:

 

If gross salary is not over $308, no withholding is necessary

If gross salary is over 308 but not over 1102, the withholding is 0 plus 10% of excess over 308

If gross salary is over 1102 but not over 3533 the withholding is 79.40 plus 12% of excess over 1102

If gross salary is over 3533 but not over 7183 the withholding is 371.12 plus 22% of excess over 3533

 

If gross salary is over 7183, they probably aren't working or me, I can't afford them ;-)

 

BTW, that 25 I put on my federal example was just for me to check that the VLOOKUP was working. It is  meaningless  as far as the rules go.

 

Paul



#8 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 12 March 2018 - 02:11 PM

Hi Paul,

 

I think you will be able to do this by using the same techniques that you're already using, just by adding a little bit more information to your table of tax rates.

 

Here's how I modified the table:

 

FederalTax.png

 

As you can see, I have added columns containing the cutoff amount and the percentage of the value over that amount that should be added. By returning the basic rate, cutoff amount and percentage using VLOOKUP functions, you should have everything you need to calculate the tax and should still be able to easily modify the tax rates.

 

I have attached a copy of your workbook with all of the formulas applied:

 

Attached File  FederalTax.xlsx   18.11KB   49 downloads

FederalTax2.png

 

To make the example easier to understand I have split the formula into separate columns, but you can see it all combined into a single formula in the COMBINED FORMULA column.

 

You can see more about using the VLOOKUP function for inexact lookups in this way in: Expert Skills Session 3 (Lesson 3-25: Use a VLOOKUP function for an inexact lookup).

 

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#9 TallPaul

TallPaul

    Newbie

  • Members
  • Pip
  • 9 posts

Posted 12 March 2018 - 06:59 PM

WOW!! That certainly does solve the problem, thanks. I wasn't aware you could use multiple VLOOKUPs in a formula like that. That is awesome to know!

And I guess I could hide that entire Fed calculation table(J2:O15) on the page that has my summary table and it would still work fine?

 

If you got this problem, would you have used this approach or are there more efficient techniques to maybe solve this problem??

 

I am just a beginner in EXCEL. I have gone thru the basic and essential books and I am just starting chapter 5 in the advanced book.

 

Thanks,

 

Paul



#10 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 13 March 2018 - 10:29 AM

Hi Paul,

 

You can actually delete the calculation table as I have placed the entire formula in column D; the table is just there to make it easier to understand how the formula works.

 

As with almost any Excel problem, there are many different ways this could be done. I chose this approach because you mentioned that the tax rates are subject to change and that you didn't want to have to rewrite your formulas whenever there is a change in tax rates.

 

If the tax rates were never going to change, this could also be done by using IF functions or the IFS function. For example, here's an IF function that calculates the highest rate of tax:

=IF(C4>=3533,371.12+((C4-3533)*22%),0)

 

As you can see, this checks whether the gross was above 3533 and then adds the basic rate of tax to 22% of the excess.

 

The IF function is covered in depth: Expert Skills Session 3 (Lesson 3-5: Use the IF logic function).

 

You could calculate the other tax rates as follows:

=IF(C4>=1102,79.4+((C4-1102)*12%),0)

=IF(C4>=308,0+((C4-308)*10%),0)

 

You could place these in separate columns or add them all together in a single formula, but you could also combine them together more efficiently using the IFS function like this:

=IFS(C4>=3533,371.12+((C4-3533)*22%),C4>=1102,79.4+((C4-1102)*12%),C4>=308,0+((C4-308)*10%),C4>=0,0)

 

The IFS function offers and efficient way to create logical formulas with multiple criteria like this one.

You can find a full explanation of IFS in: Expert Skills Session 3 (Lesson 3-28: Use the IFS function).

 

The logical formula approach makes for a tidier workbook without any additional tables, but it also makes it harder to understand and change the tax rates. The VLOOKUP approach makes tax rates easy to change, and new rates can even be added without needing to modify any formulas.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#11 TallPaul

TallPaul

    Newbie

  • Members
  • Pip
  • 9 posts

Posted 13 March 2018 - 06:09 PM

Hi Jonathan,

 

Thank you so much for your help. I learned quite a bit more about Excel.

 

I may have not been clear on the fed table as far as how to calculate the withhold when the gross amount is less than 1102 and equal to or greater than 308. If that is the case, then it is just a straight 10% of the gross. I changed the fed table from 308 to 0 on the USE and EXCESS CUTOFF and it came out perfectly!!!

 

Thanks again for your help!!

 

Paul

 

PS

I appreciate the above more efficient IFS calculations  I really like IFS!!






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users