+ Reply to Thread
Results 1 to 8 of 8

Help with nested functions

  1. #1
    Registered User
    Join Date
    07-11-2018
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    14

    Help with nested functions

    Enclosed is a copy of my current template I'm working on. Essentially I'm trying to create a spreasheet that compares gross earnings to the federal taxation index to auto populate how much federal and provincial tax to take off a pay check.

    There are 2 parts to this - one I need to determine which index it falls within and two I need the formula to compare the Claim code against what the employee has on record and adjust the tax amount accordingly.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,788

    Re: Help with nested functions

    Thanks for the workbook, but where are we looking (cell reference and tab, please)? And what exactly have you tried that is not working for you?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    07-11-2018
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    14

    Re: Help with nested functions

    In cell m4 on the third sheet. Under federal tax. I'm trying to find a way for the gross salary to compare itself to see where it falls under the index on the next page, once it determines that i want it to check which CC code the employee has to see how much tax they pay. I somehow managed to get it working, I think? I added a +4 at the end of the formula but I'm unsure why it actually works.

    =(VLOOKUP([@[GROSS ($)]],'Biweekly Federal Index'!$A$7:$N$38,(HLOOKUP('Employee Info'!$K$5,'Biweekly Federal Index'!$D$8:$N$38,1,TRUE)+4)))
    Last edited by m3d1c123; 09-03-2018 at 05:58 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    655

    Re: Help with nested functions

    Hi,

    The formula you posted above does work, but it's kind of a "round about" way of doing it.

    Try this instead:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached.
    Last edited by jtakw; 09-03-2018 at 06:16 PM.

  5. #5
    Registered User
    Join Date
    07-11-2018
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    14

    Re: Help with nested functions

    Quote Originally Posted by jtakw View Post
    Hi,

    The formula you posted above does work, but it's kind of a "round about" way of doing it.

    Try this instead:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached.
    Thanks for replying. I've completed the index so its been updated since so I'll attach a new copy.

    I am not able to get the formula you mentioned to work. It is included in the uploaded copy. Its giving me a result of 0.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    655

    Re: Help with nested functions

    You have an incorrect Cell Range.

    You have:

    =VLOOKUP([@[GROSS ($)]],'Biweekly Federal Index'!$A$8:$N$337,MATCH('Employee Info'!$K$5,'Biweekly Federal Index'!$D$7:$N$7,0))

    It should be:

    =VLOOKUP([@[GROSS ($)]],'Biweekly Federal Index'!$A$8:$N$337,MATCH('Employee Info'!$K$5,'Biweekly Federal Index'!$A$7:$N$7,0))
    Last edited by jtakw; 09-03-2018 at 06:56 PM.

  7. #7
    Registered User
    Join Date
    07-11-2018
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    14

    Re: Help with nested functions

    ohh shoot! thanks!

  8. #8
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    655

    Re: Help with nested functions

    You're welcome, thanks for the feedback, hope you get it working.
    If you need additional help, post back.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1