+ Reply to Thread
Results 1 to 14 of 14

Complex if statement returns incorrect results

  1. #1
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Complex if statement returns incorrect results

    Hi, this is my first post, please be gentle.

    I am building a fairly detailed taxation system model for a project. There are thousands of formulas (and pages), and the goal is to measure the effects of about 7 taxes across 8000 taxation units based on generalised price increases. Basically it is a really big tax calculator. This is just context, and the reason I can't post the entire file (it is big).

    One element I am using is a layered if statement to estimate taxes (that use a progressive scale, like an income tax). In a nutshell the goal is to say 'the tax value of n taxation units at $x is $y million, based on the taxation system thresholds' - a fairly normal tax calc. Problem is, the excel result differs from a simple manual test- for example, manual calculation for one transaction produces a result of $9.24m, excel yields $7.86m. I need to repeat this calculation for up to 1,000 taxation units, over a 50 year horizon (that includes price changes), and for the results to automatically adjust to changed assumptions for the end user!

    The statement is:
    =IF(GQ2=0,0,IF((GQ2/$AA2)>=TaxAss!D$12,(GQ2/$AA2)*TaxAss!D$31*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$7,(GQ2/$AA2)<TaxAss!D$14),(((GQ2/$AA2)*TaxAss!D$26)+TaxAss!D$19)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$8,(GQ2/$AA2)<TaxAss!D$15),((((GQ2/$AA2)-TaxAss!D$8)*TaxAss!D$27)+TaxAss!D$20)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$9,(GQ2/$AA2)<TaxAss!D$16),((((GQ2/$AA2)-TaxAss!D$9)*TaxAss!D$28)+TaxAss!D$21)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$10,(GQ2/$AA2)<TaxAss!D$17),((((GQ2/$AA2)-TaxAss!D$10)*TaxAss!D$29)+TaxAss!D$22)*$AA2,IF(AND((GQ2/$AA2)>=TaxAss!D$11,(GQ2/$AA2)<TaxAss!D$18),((((GQ2/$AA2)-TaxAss!D$11)*TaxAss!D$30)+TaxAss!D$23)*$AA2,IF((GQ2/$AA2)<=TaxAss!D$13,(GQ2/$AA2)*TaxAss!D$25*$AA2))))))))

    I don't think I can simplify it, while maintaining the functionality. As a reference GQ2(total value)/$AA2(#value units) is required to allow for underlying price shocks. 'TaxAss' is the taxation system variables worksheet, the cell refs are the thresholds, marginal rates and the 'cumulative dollar values and I have designed the tax time series to run left-to-right with the ability to change the tax regime (ie the reference above relates to 2014, in 2015 the taxass column is e).

    The underlying tax unit value is $450k and by rights the component: IF(AND((GQ2/$AA2)>=TaxAss!D$8,(GQ2/$AA2)<TaxAss!D$15),((((GQ2/$AA2)-TaxAss!D$8)*TaxAss!D$27)+TaxAss!D$20)*$AA2; should work as it has thresholds $301k-$501k. But the result is out, as noted...

    This may be abstract, but does anyone have an idea about what might be going wrong???

    Regards
    amk73

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Complex if statement returns incorrect results

    It appears that you are just trying to implement a tiered rate structure.
    If you can either post the values from the TaxAss sheet
    or (preferred) post a sample workbook
    I think we can provide a much simpler formula.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Complex if statement returns incorrect results

    Its easier if you use a table with the same logic as you can easily see where the result is coming from
    See attached
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Complex if statement returns incorrect results

    Thanks. What I have tried to do is isolate the sample and provide the full detail of taxass. See attached.

    The first sheet gives the AA2 value (a calculated parameter), the context for GQ2 (etc, this will be different for each row), and shows how the statement is applied (the links are broken). To demonstrate the issue, I have included the manual calc based on the legislation.

    Hope this helps.
    amk
    Attached Files Attached Files

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Complex if statement returns incorrect results

    your sample has links to another workbook and there are ref errors in your if formula
    but where are those figures coming from and where is zone 1?
    Last edited by martindwilson; 01-17-2014 at 10:51 AM.

  6. #6
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Complex if statement returns incorrect results

    Thanks, this is an interesting approach. I will have a play with this inside the model.

  7. #7
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Complex if statement returns incorrect results

    Quote Originally Posted by martindwilson View Post
    your sample has links to another workbook and there are ref errors in your if formula
    but where are those figures coming from and where is zone 1?
    Yes. Unfortunately, other than being very large, I am unable to share each part of the model, as it identifies information I am not able to share. What I have tried to do is isolate the relevant bits for the tax calculation. You can be assured that all inputs into the if statement are performing as required and the AA2 sample is a live example. If you refer to the initial post, the if statement is intact (the ref errors and links in the example are for context - the calculations start at IR2 and go through to KQ1458). The 'zone' language is poor on my part, what I meant is that the tier for a 450000 tax unit is <as shown>. This is based on the part of the if statement: IF(AND((GQ2/$AA2)>=TaxAss!D$8,(GQ2/$AA2)<TaxAss!D$15),((((GQ2/$AA2)-TaxAss!D$8)*TaxAss!D$27)+TaxAss!D$20)*$AA2; where in the example the links would actually be D7, D14, D19 and D26 respectively (and these contain the values I have used in the manual calc).

    Hope that helps.

    So the goal is to automate the tax calculation for each year, based on whether there is a value in that year, and based on the taxation system (thresholds, values etc) that prevail in that year, over a 50 year horizon.

  8. #8
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Complex if statement returns incorrect results

    Sorry to pike, I have to sleep (225am Australian time), will check thoughts in the morning. Thanks

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Complex if statement returns incorrect results

    can you just attach a sheet with say 3 years the thing is all boundaries c6:bc15 appear to be the same
    also you understand
    Logical results: Value 450000
    in zone 301-500, so 8100 + (450k-301k)*4.5%
    = 14805
    Total for 624 9238320
    i have no idea wher you get those values from
    Last edited by martindwilson; 01-17-2014 at 12:51 PM.

  10. #10
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Complex if statement returns incorrect results

    Quote Originally Posted by martindwilson View Post
    can you just attach a sheet with say 3 years the thing is all boundaries c6:bc15 appear to be the same
    also you understand
    Logical results: Value 450000
    in zone 301-500, so 8100 + (450k-301k)*4.5%
    = 14805
    Total for 624 9238320
    i have no idea wher you get those values from
    Hello again. So the fields you refer to (c6:bc15) are the same at this moment. One of the issues to test when he model is complete is teh senstivity of the results to changes in the tax thresholds over time. Without data in cells b12:b17 and b24:b30 the data will be the same; alterativley any data in those cells will adjust the entire system. It is not beautiful I admit, but it will achieve the objective.

    With the calculation: the value of $450,000 is derived in the 'Value Sample' sheet at I4 (just repeated at BJ6. 'zone 301-500' is as mentioned just the tier for a $450,000 tax event. The $8,100 is in TaxAss at d19. The $301k is in TaxAss at D7 (the upper boundary of 500k is at d14). The 4.5% is at TaxAss d26. The number 624 is the number of taxation units or events (it is the same as the AA2 reference which in the example is at A3.

    What the manual calculation does is what the i statement should do, which is to select the 301-500k tier and calculate as shown. Cell BJ3 is the paste of the result the model calculates ($7.86m), cell bj9 is the manual result ($9.24m); and I have caluclated the difference 'Error? at $1.38m.

    Hope that helps.

  11. #11
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Complex if statement returns incorrect results

    Quote Originally Posted by amk73 View Post
    Hello again. So the fields you refer to (c6:bc15) are the same at this moment. One of the issues to test when he model is complete is teh senstivity of the results to changes in the tax thresholds over time. Without data in cells b12:b17 and b24:b30 the data will be the same; alterativley any data in those cells will adjust the entire system. It is not beautiful I admit, but it will achieve the objective.

    With the calculation: the value of $450,000 is derived in the 'Value Sample' sheet at I4 (just repeated at BJ6. 'zone 301-500' is as mentioned just the tier for a $450,000 tax event. The $8,100 is in TaxAss at d19. The $301k is in TaxAss at D7 (the upper boundary of 500k is at d14). The 4.5% is at TaxAss d26. The number 624 is the number of taxation units or events (it is the same as the AA2 reference which in the example is at A3.

    What the manual calculation does is what the i statement should do, which is to select the 301-500k tier and calculate as shown. Cell BJ3 is the paste of the result the model calculates ($7.86m), cell bj9 is the manual result ($9.24m); and I have caluclated the difference 'Error? at $1.38m.

    Hope that helps.
    Thanks for help. Upon reflection it turns out to be an ID-10-T error. Manual calculation did not draw on the advancing
    tax scales, so the apples don't compare to the oranges.

    I will resume my love affair with excel now.

    Regards
    amk73

  12. #12
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Complex if statement returns incorrect results

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  13. #13
    Registered User
    Join Date
    01-17-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Complex if statement returns incorrect results

    Quote Originally Posted by arlu1201 View Post
    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    Thanks for the tip. Yes, am new to this forum idea. Perhaps I am missing it, but I can't see the stars? Will follow procedure next time.

  14. #14
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Complex if statement returns incorrect results

    The star icon has the text "Add Reputation" next to it. Its to the left lower portion of the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF STATEMENT returns False, even though the results are true
    By Biolu527 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2013, 11:21 AM
  2. [SOLVED] MOD(x,1) returns 1 (*Incorrect*)
    By lokanu in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-18-2012, 10:20 AM
  3. Query searching returns incorrect results
    By vampyr07au in forum Access Tables & Databases
    Replies: 18
    Last Post: 06-02-2011, 11:35 AM
  4. Query in Access returns incorrect results
    By johnhurgeton in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2010, 11:31 AM
  5. Replies: 1
    Last Post: 03-09-2006, 04:00 PM

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