Calculating tax Formula

D

DWright

I am constructing a payroll spreadsheet. In the cell that calculates the
Federal TaxI have the following:
=if(i7<100,(i7*.04769)=if(i7>101<150,(i7*.05693))). The value in i7 is 140
and it returns a FALSE in cell i7 instead of a dollar amount.
Any help would be greaatly appreciated.
 
H

Harald Staff

Hi

IFs are made like
=If(Test, test true then this, Otherwise this)

You missed a comma after "if true then this", and you don't have
an"otherwise"after the second test..
Also; i7>101<150 is not valid syntax.

HTH. Best wishes Harald
 
A

Arvi Laanemets

Hi

Maybe for first $100 the percent is 47.69%, for next $50 it is 56.93% etc.
Otherwise netto from $100 is $52.31, but from $101 it is $42.50 ???


Arvi Laanemets
 
M

Myrna Larson

The equal sign in the middle of the formula isn't correct. Should probably be
a comma. And this part is not correct:

i7>101<150

This should work, but you don't say what to return if i7 is greater than 150.

=if(i7<100,i7*.04769,if(i7<150,i7*.05693))

Remember, you can have only 7 nested IF formulas. You might find it easier to
write the formulas if you create a table to use with VLOOKUP. This table would
have the amounts in the 1st column and the percentage in the 2nd.
 
D

DWright

That did it and I did add shat if i7 is greater than 150
Could you explain How to create a table of tax percentages to use with
VLOOKUP?
Thank you.
 
D

DWright

Yes I have. If I could get a example with actual numbers in it I might be
able to understand it better.
 
A

Arvi Laanemets

Hi

Check this.
=CHOOSE(MATCH(A2,{0,100,150},1),0,4.769,7.6155)+(A2-CHOOSE(MATCH(A2,{0,100,1
50},1),0,100,150))*CHOOSE(MATCH(A2,{0,100,150},1),4.769%,5.693%,10%)

First CHOOSE(..) returns the tax value at percentage change level, your sum
in I7 is next or equal (calculated them manually);
Second CHOOSE(..) returns percentage change level, your sum in I7 is next
or equal;
Third CHOOSE(..) returns tax percent for percentage change level, your sum
in I7 is next or equal.

I have seen in some of Excel NG's easier formulas too, but seemingly I
haven't sved any of them.
 
Top