nested statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having trouble writing what I think is a nested formula :

If A is >=460 and A is <=1088 Then this formula should be used:
(A-460)*2%+4.60

If A is >=1089 and A is <=1716 Then this formula should be used
(A-1088)*4%+17.16

I want the response returned to one cell. This formula is to calculate CA
state taxes.
 
Sherri,

You don't say what happens outside of the figures you gave but try:

=IF(AND(A1>1088,A1<=1716),(A1-1089)*4%+17.6,IF(AND(A1>=460,A1<=1088),(A1-460
)*2%+4.6,"Outside Range"))


HTH

Sandy
 
Whoops, forgot the third argument

=IF(A<460,"Below Range",IF(A<=1088,A*2%-4.6,IF(A<=1716,A*4%-26.36,"Above
Range"))

But I think you'll find the technique at my link easier to maintain,
especially if you use tables instead of hardcoding the rates.
 
Hi People: Sorry I wasn't more clear. Here goes:

I came up with this formula and it seems to work, but if I test it by
plugging in the variable Q15=2500, it doesn't return the correct calculation
according to the formula contained in the OR statement
{=IF(AND(Q15>=858,Q15<=2490),(sum(Q15-858)*15%+55),(OR(Q15-2490)*25%+299.8))}

My second formula will not calculate correctly either:

=IF(AND(Q15>=460,Q15<=1088),(SUM(Q15-460)*2%+4.6),(OR(Q15-1088)*4%+17.16))
Where Q15=1179.57, the answer should be "20.82." But it keeps returning
"17.20."

Thanks for your help
 
First, your SUM()'s are unnecessary, and your OR()s return a TRUE or
FALSE value, which when multiplied, are interpreted by XL as 1 or 0,
respectively.

Try:


=IF(AND(Q15>=858,Q15<=2490),(Q15-858)*15%+55,(Q15-2490)*25%+299.8)

Second formula:

=IF(AND(Q15>=460,Q15<=1088),(Q15-460)*2%+4.6,(Q15-1088)*4%+17.16)

which, if you really want 20.82, should be

=ROUND(IF(AND(Q15>=460,Q15<=1088),(Q15-460)*2%+4.6,(Q15-1088)*4%+17.16),2
)
 
Back
Top