Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Perth, Western Australia
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel lookups for Word gurus (2000 SR-1)

    This is my first posting, so please be gentle!

    Although a Word expert, I'm an Excel novice - I can do the basics, but not much more. Can someone help me with what I think is a basic lookup task? I cannot figure it out (that half of brain is underdeveloped after years of writing in Word!).

    I have a gross salary figure on Sheet 1of my spreadsheet. I want it to look up the appropriate tax rate on a very simple table on Sheet 2 so I can have the taxation and various other deductions alongside the gross figure. Programming 101 stuff, I reckon.

    The table has a number of rows, each of which corresponds to an Australian income tax bracket. The columns contain (in this order) the top end of each braket, the basic tax 'lump sum' for that bracket, and the tax rate for each dollar in excess of the bottom end of the bracket.

    All I want Excel to do is figure out which row/bracket the input data corresponds to, and haul out the lump sum & rate figures for the calculations. (I have the neural firepower to do those solo!)

    I expected that this would be a simple chore, but then I made the mistake of delving into Excel's Help...

    Any tips will be gratefully accepted!

    Best regards

    Neil

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups for Word gurus (2000 SR-1)

    If the salary is on cell A1 and the table with tax rates is the range 'Sheet2'!A1:F10, then:

    =VLOOKUP(A1,'Sheet2'!A1:F10,2,true)

    returns the appropriate row, second column of the tax range. Note that the taxe rates have to be sorted ascending. Try out if the switch over point to the next taxrate is correct by entering salaries close to the "switch-over" points.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups for Word gurus (2000 SR-1)

    I created a table on sheet2 with Amount, Tax, $1 columns.

    I selected all of the cells in the range and hit Insert-Name-Define and called it Taxes.

    On sheet1, column A is Salary, B is Tax.

    I put 23575 in A2, and use this formula in B2:

    =VLOOKUP(ROUND(A2,-3),Taxes,2)+MOD(A2,1000)*VLOOKUP(A2,Taxes,3)

    Hope I understood your question.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups for Word gurus (2000 SR-1)

    Close. It works if the salary is in the defined range.

    There is a minimum range, below which no tax is paid. This gives a "#N/A" error if the number is below this range.

    Also, there is a salary, above which, all tax is payable at the maximum rate. I can't get this formula to work for that condition. That might be because I defined the name past the maximum range?

    I've done this with a function- though your formula looks promising:<pre>Public Function CalculateTax(value)
    Dim i As Integer
    Dim lngSalary As Long
    Dim dblTax As Double
    lngSalary = value

    i = 1
    dblTax = -1
    With ActiveWorkbook.Sheets("Sheet2")
    Do Until dblTax >= 0
    If lngSalary <= .Cells(i, 1) Or Trim$(.Cells(i, 1)) = "" Then
    If i = 1 Then
    dblTax = .Cells(i, 1)
    Else
    dblTax = (lngSalary - .Cells(i - 1, 1)) * ._
    Cells(i - 1, 3) + .Cells(i - 1, 2)
    End If
    End If
    i = i + 1
    Loop

    End With
    CalculateTax = dblTax

    End Function</pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups for Word gurus (2000 SR-1)

    The trick is to change your table. Vlookup does a less then comparison. If you will include a row that starts with zero and one that is .01 cents more then your top value you should get the correct result. for example if I use the following table:

    0 0
    100 .1
    200 .25
    300 .75

    Anything less then 100 would return zero. Anything more then 300 would return .75. The formula I used looks like this =VLOOKUP(K10,L4:M7,2) Where the value I am looking up is in k10, the table is in l4 to M7 and I want the second coloum.

  6. #6
    Star Lounger
    Join Date
    Sep 2001
    Location
    Perth, Western Australia
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups for Word gurus (2000 SR-1)

    Thanks for your suggestion - this is pretty close!

  7. #7
    Star Lounger
    Join Date
    Sep 2001
    Location
    Perth, Western Australia
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel lookups for Word gurus (2000 SR-1)

    Thanks for your prompt assistance with this - it's much appreciated!

Posting Permissions

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