nested statements
 

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.

Can you confirm please what answer should be returned if A < 460 or if A
1716


--
Return email address is not as DEEP as it appears
"Sherri" <Sherri @discussions.microsoft.com wrote in message
...
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(A11088,A1<=1716),(A1-1089)*4%+17.6,IF(AND(A1=460,A1<=1088),(A1-460
)*2%+4.6,"Outside Range"))


HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Sherri" <Sherri @discussions.microsoft.com wrote in message
...
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.

One way:


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

A different, somewhat more flexible approach:

http://www.mcgimpsey.com/excel/variablerate.html



In article ,
Sherri <Sherri @discussions.microsoft.com wrote:

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.

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.


In article ,
JE McGimpsey wrote:

One way:


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

A different, somewhat more flexible approach:

http://www.mcgimpsey.com/excel/variablerate.html



In article ,
Sherri <Sherri @discussions.microsoft.com wrote:

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.

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

"Sherri" wrote:

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.

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
)





In article ,
Sherri wrote:

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

"Sherri" wrote:

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.