VLOOKUP Formula Help

Posted by Kay on February 06, 2002 10:32 AM

I have a formula to look at a table. this is the table setup..I have two tables.. one is single and one is for married.. they are both set up the same..

0---------270.00----0--0
270.00---1332.50--.06--17.00
1332.50--2740.00--.07--30.18
2740.00--4395.80--.08--45.55
4395.80-- --.09--49.32

My formula is

=SUM(R7-(G7*66.67))*IF((E7)="S",VLOOKUP(R7,State_Single,3),VLOOKUP(R7,State_Married,3))-IF((E7)="S",VLOOKUP(R7,State_Single,4),(VLOOKUP(R7,State_Married,4)))

I am getting the correct answers for most of the cells.. In some cells where no tax should be calculated it is giving me a negative number instead of a 0..

Any suggestions??
Thanks for your time..

Posted by Aladin Akyurek on February 06, 2002 11:03 AM

Care to post input values for which you get a neg result when E7 is S, that is, values for R7 and G7?

=======

Posted by Kay on February 06, 2002 1:13 PM

Input values

G7 reflects #of Dependents = 3
R7 reflects Adjusted gross pay.

What is happening is I am getting some correct results.. in other cells where it should come up with a 0 it is returning a neg. number.. for example... -1.10

Posted by Aladin Akyurek on February 06, 2002 1:24 PM

Re: Input values

Kay, I just need a concrete value for G7 and a concrete value for R7 when E7 = "S", a combination that leads to a neg value instead of zero when used in the formula you posted.

=======

Posted by Kay on February 06, 2002 1:50 PM

values

the value in G7 is 2
the value in R7 is 365.07
Returns a value of -1.10

Posted by Aladin Akyurek on February 06, 2002 2:34 PM

Re: values

Thanks. I get also a neg number (but a different one, using the table you posted, which I took to be the State_Single table).

First, I'd suggest a simplification of your formula:

=(R7-(G7*66.67))*VLOOKUP(R7,IF(E7="S",State_Single,State_Married),3)-VLOOKUP(R7,IF(E7="S",State_Single,State_Married),4)

Don't be alarmed: It's exactly equivalent to yours.

If you're certain that the values in your State_Single and State_Married are correct and the maths that the above formula uses for computing the taxes are correct (you said: "getting the correct answers for most of the cells"), then you can avoid getting neg values by modifying the formula above (or yours if you prefer) as follows:

=MAX(0,(R7-(G7*66.67))*VLOOKUP(R7,IF(E7="S",State_Single,State_Married),3)-VLOOKUP(R7,IF(E7="S",State_Single,State_Married),4))

Would you check whether the one with MAX behaves the way you want it to behave?

=============

Posted by Kay on February 06, 2002 3:04 PM

Thank You

The one with the Max worked perfectly. I have been trying to figure this out for days...

THANK YOU SO MUCH!!!!!! :)