Results 1 to 7 of 7

20010910, 09:04 #1
 Join Date
 Sep 2001
 Location
 Perth, Western Australia
 Posts
 89
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel lookups for Word gurus (2000 SR1)
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

20010910, 11:14 #2
 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 SR1)
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 "switchover" points.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20010910, 11:35 #3
 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 SR1)
I created a table on sheet2 with Amount, Tax, $1 columns.
I selected all of the cells in the range and hit InsertNameDefine 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.

20010910, 11:53 #4
 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 SR1)
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.

20010910, 14:17 #5
 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 SR1)
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.

20010910, 22:38 #6
 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 SR1)
Thanks for your suggestion  this is pretty close!

20010910, 22:39 #7
 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 SR1)
Thanks for your prompt assistance with this  it's much appreciated!