Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mini-editor for nested IF's in Excel | Excel Discussion (Misc queries) | |||
Q: Nested if | Excel Discussion (Misc queries) | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
Problem with data using IF and Nested IF statements possibly??? | Excel Discussion (Misc queries) | |||
Macro to find and delete all FALSE statements | Excel Discussion (Misc queries) |