Excel lookups for Word gurus (2000 SR-1)

[techspeak]

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

[pieterse]

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.

[Dreamboat]

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.

[gwhitfield]

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>

[mmckissock]

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.

[techspeak]

Thanks for your suggestion - this is pretty close!

[techspeak]

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