Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sherri
 
Posts: n/a
Default 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   Report Post  
Jack Sheet
 
Posts: n/a
Default

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   Report Post  
Sandy Mann
 
Posts: n/a
Default

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   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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   Report Post  
Sherri
 
Posts: n/a
Default

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   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mini-editor for nested IF's in Excel Erik Neu Excel Discussion (Misc queries) 3 January 5th 05 12:30 AM
Q: Nested if JIM.H. Excel Discussion (Misc queries) 2 December 31st 04 10:17 PM
Nested IF limit or Open parentheses limit Fred Excel Discussion (Misc queries) 5 December 23rd 04 04:34 PM
Problem with data using IF and Nested IF statements possibly??? Ajay Excel Discussion (Misc queries) 2 December 9th 04 10:23 AM
Macro to find and delete all FALSE statements Woody13 Excel Discussion (Misc queries) 3 December 9th 04 12:16 AM


All times are GMT +1. The time now is 12:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"