Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating tax Formula
I am constructing a payroll spreadsheet. In the cell that calculates the
Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
#2
|
|||
|
|||
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; i7101<150 is not valid syntax. HTH. Best wishes Harald "DWright" skrev i melding ... I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
#3
|
|||
|
|||
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 "DWright" wrote in message ... I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
#4
|
|||
|
|||
The equal sign in the middle of the formula isn't correct. Should probably be
a comma. And this part is not correct: i7101<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. "DWright" wrote in message ... I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
#5
|
|||
|
|||
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. "Myrna Larson" wrote: The equal sign in the middle of the formula isn't correct. Should probably be a comma. And this part is not correct: i7101<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. "DWright" wrote in message ... I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
#6
|
|||
|
|||
Have you looked at Help for this function? It includes an example.
On Tue, 30 Nov 2004 04:27:02 -0800, "DWright" wrote: 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. "Myrna Larson" wrote: The equal sign in the middle of the formula isn't correct. Should probably be a comma. And this part is not correct: i7101<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. "DWright" wrote in message ... I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
#7
|
|||
|
|||
Yes I have. If I could get a example with actual numbers in it I might be
able to understand it better. "Myrna Larson" wrote: Have you looked at Help for this function? It includes an example. On Tue, 30 Nov 2004 04:27:02 -0800, "DWright" wrote: 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. "Myrna Larson" wrote: The equal sign in the middle of the formula isn't correct. Should probably be a comma. And this part is not correct: i7101<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. "DWright" wrote in message ... I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
#8
|
|||
|
|||
Hi
Check this. =CHOOSE(MATCH(A2,{0,100,150},1),0,4.769,7.6155)+(A 2-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. -- Arvi Laanemets (When sending e-mail, use address arvil<Attarkon.ee) "DWright" wrote in message ... Yes I have. If I could get a example with actual numbers in it I might be able to understand it better. "Myrna Larson" wrote: Have you looked at Help for this function? It includes an example. On Tue, 30 Nov 2004 04:27:02 -0800, "DWright" wrote: 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. "Myrna Larson" wrote: The equal sign in the middle of the formula isn't correct. Should probably be a comma. And this part is not correct: i7101<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. "DWright" wrote in message ... I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7101<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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell reference in a formula is called | Excel Discussion (Misc queries) | |||
formula help | Charts and Charting in Excel | |||
Data Label Value in Formula? | Charts and Charting in Excel | |||
hyperlink formula | Excel Discussion (Misc queries) | |||
formula for doing Smith Jhon to Jhon Smith ? | Excel Discussion (Misc queries) |