if, if/and, Vlookup...What to do

[Andmor]

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

 

[joemeshuggah]

you could use an if statement...

=IF(AND(D5>=0,D5<=37106),0.7985,IF(AND(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...

 

[David Biddulph]

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.01,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.7585,IF(D5<=65345,0.6885,"")))....VLOOKUP would be another option, and perhaps preferable.--David Biddulph"joemeshuggah" <[email protected]> wrote in messageyou could use an if statement...>>=IF(AND(D5>=0,D5<=37106),0.7985,IF(AND(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.

 

[Per Jessen]

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,.........IF(D5<=B8,C8,C9))))

Regards,
Per

 

[Andmor]

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?

 

[joemeshuggah]

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]

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"

 

[Andmor]

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.

 

[joemeshuggah]

try this...be sure to test...
=IF(AND(D5>=0,D5<=37106),D5*0.7985,IF(AND(D5>=37106.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,IF(AND(D5>=76986.01,D5<=81941),D5*0.6059,IF(AND(D5>=81941.01,D5<=127021),D5*0.5659,IF(D5>=127021.01,D5*0.5359,""))))))))

 

[Andmor]

thanks joe, that seems to have worked bang on!

 

[David Biddulph]

I think you could shorten
=IF(AND(D5>=0,D5<=37106),D5*0.7985,IF(AND(D5>=37106.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,IF(AND(D5>=76986.01,D5<=81941),D5*0.6059,IF(AND(D5>=81941.01,D5<=127021),D5*0.5659,IF(D5>=127021.01,D5*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,56.59%,53.59%))))))))

 

[מיכ×ל (מיקי) ×בידן]

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"

 

[מיכ×ל (מיקי) ×בידן]

Although the "time of night" - will it be a mistake to suggest this:
=F28*INDEX(C2:C9,MATCH(F28,A2:A9))
Micky

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"

thanks joe, that seems to have worked bang on!