Help with a formula

[Suzie]

Hi

I am doing an assessment for TAFE and need some help doing a formula, so if
there is anybody out there willing to help, I would really appreciate it.



Using lookups, vlookups of If's - we need to work out the tax payable on the
gross wage. Currently my gross wages are in Column H starting at Row 5.
Below is the tax table we have to use. I can do the first part using a
lookup fine, but just cannot seem to add the second part in about the 30
cents so that it work. Any help gratefully accepted.



Suzie



Taxation Rates for Payroll

if weekly pay is then

between and deduct



up to $95 $0.00

$96 $345 $46.00

$346 $480 $63.00

$481 $672 $96.00

$673 $961 $183.00

$962 or over $308.00



In addition, if an employee's gross wage is over

$480 per week, then they must pay an additional

30 cents in the dollar for every dollar over $480.

 

[Earl Kiosterud]

Suzie,

=VLOOKUP(H5,$A$1:$C$6,3) + MAX(0,(H5-480))*30

I've put your table thus. You'll need to modify the VLOOKUP to suit your
location:

A B C
1 0 95 0
2 96 345 46
3 346 480 63
4 481 672 96
etc.

As a matter of programming practice, it's a good idea to put the $30 in a
cell, name it (Insert - Name - Define), then use the name in the formula in
place of the 30. It clarifies your formula values and makes it easier when
that figure changes.

 

[Suzie]

Earl, you are a lifesaver - Thank you so very much for your help. Have
modified it to suit and works perfectly.

Thanks very much again.
Suzie