| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
if, if/and, Vlookup...What to do
I am trying to find out net income. in D5 I have the gross income and in G5
is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
you could use an if statement...
=IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
I doubt whether the OP really intended that there would be a gap between
37106 and 37106.01, or between 40970 and 40970.01, and so on. You might therefore change =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....to =IF(D5<0,"",IF(D5<=37106,0.7985,IF(D5<=40970,0.758 5,IF(D5<=65345,0.6885,"")))....VLOOKUP would be another option, and perhaps preferable.--David Biddulph"joemeshuggah" wrote in ... you could use an if statement...=IF(AND(D5=0,D5<=37106),0.7985,IF(A ND(D5=37106.01,D5<=40970),0.7585,IF(AND(D5=40970 .01,D5<=65345),0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and inG5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against togive net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
Hi
I would just assume that D5 will always be 0, and use upper values only: With upper limit values in B2:B9, and corresponding percentage in column C, try this: =IF(D5<=B2,C2,IF(D5<=B3,C3,IF(D5<=B4,C4,.........I F(D5<=B8,C8,C9)))) Regards, Per "joemeshuggah" skrev i meddelelsen ... you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
I do not need it to return the tax rate I need it to return net income. e.g.
$70,000 of gross income becomes $46,914 of net income. -- A. "Per Jessen" wrote: Hi I would just assume that D5 will always be 0, and use upper values only: With upper limit values in B2:B9, and corresponding percentage in column C, try this: =IF(D5<=B2,C2,IF(D5<=B3,C3,IF(D5<=B4,C4,.........I F(D5<=B8,C8,C9)))) Regards, Per "joemeshuggah" skrev i meddelelsen ... you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
I don't mind typing them all in but if I can just use the range i have
already typed in that would save me some time. How do you hardcode them? -- A. "joemeshuggah" wrote: you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
by hardcoding i meant typing in the values of your range...e.g. 37106,
37106.01, etc etc...it would probably be better to use the cell reference tied to each number...either way you would have to type either the value or the cell reference "Andmor" wrote: I don't mind typing them all in but if I can just use the range i have already typed in that would save me some time. How do you hardcode them? -- A. "joemeshuggah" wrote: you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
Joe I have tried your formula however once I add in the next set of data I
get the message that says "you've entered too many arguments for this function" -- A. "joemeshuggah" wrote: you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
try this...be sure to test...
=IF(AND(D5=0,D5<=37106),D5*0.7985,IF(AND(D5=3710 6.01,D5<=40970),D5*0.7585,IF(AND(D5=40970.01,D5<= 65345),D5*0.6885,IF(AND(D5=65345.01,D5<=74214),D5 *0.6702,IF(AND(D5=74214.01,D5<=76986),D5*0.6461,I F(AND(D5=76986.01,D5<=81941),D5*0.6059,IF(AND(D5 =81941.01,D5<=127021),D5*0.5659,IF(D5=127021.01,D 5*0.5359,"")))))))) "Andmor" wrote: Joe I have tried your formula however once I add in the next set of data I get the message that says "you've entered too many arguments for this function" -- A. "joemeshuggah" wrote: you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
thanks joe, that seems to have worked bang on!
-- A. "joemeshuggah" wrote: try this...be sure to test... =IF(AND(D5=0,D5<=37106),D5*0.7985,IF(AND(D5=3710 6.01,D5<=40970),D5*0.7585,IF(AND(D5=40970.01,D5<= 65345),D5*0.6885,IF(AND(D5=65345.01,D5<=74214),D5 *0.6702,IF(AND(D5=74214.01,D5<=76986),D5*0.6461,I F(AND(D5=76986.01,D5<=81941),D5*0.6059,IF(AND(D5 =81941.01,D5<=127021),D5*0.5659,IF(D5=127021.01,D 5*0.5359,"")))))))) "Andmor" wrote: Joe I have tried your formula however once I add in the next set of data I get the message that says "you've entered too many arguments for this function" -- A. "joemeshuggah" wrote: you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
If you don't mind using a simple helper-column - it can be simplified to:
http://img521.imageshack.us/img521/55/nonamenh.png I did not check many gross incomes - you should do that. My suggestion eliminates the need to use "Hard-Coding" within the formula. Micky והמשך/י, *א, לקרוא את השורה הבאה: *********** אם תגובתי עזרה לחץ/י, *א, על <כן בפס האופקי התחתון! *********** מיכאל אבידן מ*הל פורום "אופיס" ב"תפוז" [Microsoft" Most Valuable Professional [MVP" "Andmor" wrote: thanks joe, that seems to have worked bang on! -- A. "joemeshuggah" wrote: try this...be sure to test... =IF(AND(D5=0,D5<=37106),D5*0.7985,IF(AND(D5=3710 6.01,D5<=40970),D5*0.7585,IF(AND(D5=40970.01,D5<= 65345),D5*0.6885,IF(AND(D5=65345.01,D5<=74214),D5 *0.6702,IF(AND(D5=74214.01,D5<=76986),D5*0.6461,I F(AND(D5=76986.01,D5<=81941),D5*0.6059,IF(AND(D5 =81941.01,D5<=127021),D5*0.5659,IF(D5=127021.01,D 5*0.5359,"")))))))) "Andmor" wrote: Joe I have tried your formula however once I add in the next set of data I get the message that says "you've entered too many arguments for this function" -- A. "joemeshuggah" wrote: you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if, if/and, Vlookup...What to do
I think you could shorten
=IF(AND(D5=0,D5<=37106),D5*0.7985,IF(AND(D5=3710 6.01,D5<=40970),D5*0.7585,IF(AND(D5=40970.01,D5<= 65345),D5*0.6885,IF(AND(D5=65345.01,D5<=74214),D5 *0.6702,IF(AND(D5=74214.01,D5<=76986),D5*0.6461,I F(AND(D5=76986.01,D5<=81941),D5*0.6059,IF(AND(D5 =81941.01,D5<=127021),D5*0.5659,IF(D5=127021.01,D 5*0.5359,"")))))))) to =IF(D5<0,"",D5*IF(D5<=37106,79.85%,IF(D5<=40970,75 .85%,IF(D5<=65345,68.85%,IF(D5<=74214,67.02%,IF(D5 <=76986,64.61%,IF(D5<=81941,60.59%,IF(D5<=127021,5 6.59%,53.59%)))))))) -- David Biddulph "joemeshuggah" wrote in message ... try this...be sure to test... =IF(AND(D5=0,D5<=37106),D5*0.7985,IF(AND(D5=3710 6.01,D5<=40970),D5*0.7585,IF(AND(D5=40970.01,D5<= 65345),D5*0.6885,IF(AND(D5=65345.01,D5<=74214),D5 *0.6702,IF(AND(D5=74214.01,D5<=76986),D5*0.6461,I F(AND(D5=76986.01,D5<=81941),D5*0.6059,IF(AND(D5 =81941.01,D5<=127021),D5*0.5659,IF(D5=127021.01,D 5*0.5359,"")))))))) "Andmor" wrote: Joe I have tried your formula however once I add in the next set of data I get the message that says "you've entered too many arguments for this function" -- A. "joemeshuggah" wrote: you could use an if statement... =IF(AND(D5=0,D5<=37106),0.7985,IF(AND(D5=37106.0 1,D5<=40970),0.7585,IF(AND(D5=40970.01,D5<=65345) ,0.6885,"")))....and so on you could also use cell references instead of hardcoding the ranges... "Andmor" wrote: I am trying to find out net income. in D5 I have the gross income and in G5 is where the net pay should be listed. The following are the various tax brackets along with the percentage it needs to be multiplied against to give net pay. I can't figure out what formula to use. 0.00 37,106.00 79.85 37,106.01 40,970.00 75.85 40,970.01 65,345.00 68.85 65,345.01 74,214.00 67.02 74,214.01 76,986.00 64.61 76,986.01 81,941.00 60.59 81,941.01 127,021.00 56.59 127,021.01 53.59 -- A. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
