Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Mutiple IF(AND) too many conditions
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Mutiple IF(AND) too many conditions
Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0 where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,651
Default Mutiple IF(AND) too many conditions
No, you are not on the right track.
AND(A25<0) is just the same as (A25<0), as you haven't told Excel what you
want to AND with (A25<0). It isn't clear what you were trying to do.
If you need Excel help on the syntax of the AND function, look up AND in
Excel help.

You've also got a number of unnecessary parentheses, such as around
(-C25/B25) and (C25/B25). These won't do any harm, but just make it more
difficult to read & check the formula.

You might, therefore, simplify
=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))
to
=IF(B25=0,IF(A25<0,-1,IF(A250,1,0)),IF(C25=0,IF(B250,C25/B25,IF(B25<0,-C25/B25,IF(C25<=0,IF(B25<0,-C25/B25,IF(B250,C25/B25)))))))
Is that what you were trying to achieve?
--
David Biddulph

"rgl2sa" wrote in message
...
Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to
be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0
where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,520
Default Mutiple IF(AND) too many conditions
In D2 try the below formula.....which give the (current results)

=IF(AND(A2=C2,A2<0),SIGN(A2),IF(B2=0,0,C2/ABS(B2)))

If this post helps click Yes
---------------
Jacob Skaria


"rgl2sa" wrote:

Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0 where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,520
Default Mutiple IF(AND) too many conditions
Forgot to mention to format column D to Percentage

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

In D2 try the below formula.....which give the (current results)

=IF(AND(A2=C2,A2<0),SIGN(A2),IF(B2=0,0,C2/ABS(B2)))

If this post helps click Yes
---------------
Jacob Skaria


"rgl2sa" wrote:

Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0 where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
  
Posts: 1,998
Default Mutiple IF(AND) too many conditions
Maybe this, formatted as percentage and copied down

=--C2/100

Vaya con Dios,
Chuck, CABGx3



"rgl2sa" wrote:

Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0 where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Mutiple IF(AND) too many conditions
YES - you have saved me from going crazy! What does the ABS do?

THANK YOU SO MUCH!

"Jacob Skaria" wrote:

In D2 try the below formula.....which give the (current results)

=IF(AND(A2=C2,A2<0),SIGN(A2),IF(B2=0,0,C2/ABS(B2)))

If this post helps click Yes
---------------
Jacob Skaria


"rgl2sa" wrote:

Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0 where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Mutiple IF(AND) too many conditions
Your solution is close to what I need...but not quite. The advise is
extremely useful though! Thank you!

"David Biddulph" wrote:

No, you are not on the right track.
AND(A25<0) is just the same as (A25<0), as you haven't told Excel what you
want to AND with (A25<0). It isn't clear what you were trying to do.
If you need Excel help on the syntax of the AND function, look up AND in
Excel help.

You've also got a number of unnecessary parentheses, such as around
(-C25/B25) and (C25/B25). These won't do any harm, but just make it more
difficult to read & check the formula.

You might, therefore, simplify
=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))
to
=IF(B25=0,IF(A25<0,-1,IF(A250,1,0)),IF(C25=0,IF(B250,C25/B25,IF(B25<0,-C25/B25,IF(C25<=0,IF(B25<0,-C25/B25,IF(B250,C25/B25)))))))
Is that what you were trying to achieve?
--
David Biddulph

"rgl2sa" wrote in message
...
Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to
be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0
where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%





.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Mutiple IF(AND) too many conditions
Ah, I see... yes that would work using those numbers in the example column B,
but it would not always hold true. Thanks for that suggestion!

"CLR" wrote:

Maybe this, formatted as percentage and copied down

=--C2/100

Vaya con Dios,
Chuck, CABGx3



"rgl2sa" wrote:

Hi, I may have too many conditions, but I am hoping to achievs the desired
Var% as indicated in column D. The goal is to always get the the 'Var%' to be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0 where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,651
Default Mutiple IF(AND) too many conditions
ABS is a standard Excel function. You'll find details if you type ABS into
Excel help.
--
David Biddulph

"rgl2sa" wrote in message
...
YES - you have saved me from going crazy! What does the ABS do?

THANK YOU SO MUCH!

"Jacob Skaria" wrote:

In D2 try the below formula.....which give the (current results)

=IF(AND(A2=C2,A2<0),SIGN(A2),IF(B2=0,0,C2/ABS(B2)))

If this post helps click Yes
---------------
Jacob Skaria


"rgl2sa" wrote:

Hi, I may have too many conditions, but I am hoping to achievs the
desired
Var% as indicated in column D. The goal is to always get the the 'Var%'
to be
the same sign (ie +/-) as the 'Variance' except when the 'Budget' = 0
where
another set of conditions apply. The closest I can get is:

=IF(B25=0,IF(AND(A25<0),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B25<0),(-C25/B25),IF(C25<=0,IF(AND(B25<0),(-C25/B25),IF(AND(B250),(C25/B25))))))))

Am I on track and can anyone add to this - or is there a totally
different
approach that someone can suggest?

Thanks!!!

Actual Budget Variance Var%
-150 -100 -50 -50.00%
0 0 0 0.00%
150 0 150 100.00%
-150 0 -150 -100.00%
-150 100 -250 -250.00%
150 100 50 50.00%
-100 -100 0 0.00%
100 100 0 0.00%
-101 -100 -1 -1.00%
0 0 0 0.00%
1 0 1 100.00%
-1 0 -1 -100.00%
-1 100 -101 -101.00%
101 100 1 1.00%
-100 -100 0 0.00%
100 100 0 0.00%
99 100 -1 -1.00%
-99 -100 1 1.00%
-500 -100 -400 -400.00%
500 100 400 400.00%
-500 100 -600 -600.00%
500 -100 600 600.00%





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Running mutiple excel sheets on mutiple computers Lost Excel Discussion (Misc queries) 3 January 16th 09 12:32 AM
mutiple if formulas JaneSmith Excel Worksheet Functions 1 June 18th 08 01:49 AM
Mutiple match criteria Peter[_2_] Excel Discussion (Misc queries) 2 April 3rd 07 03:34 PM
mutiple regression help happycow Excel Discussion (Misc queries) 1 July 30th 05 04:47 AM
Combine Mutiple Worksheets into one Dean F Excel Discussion (Misc queries) 11 November 27th 04 04:18 PM


All times are GMT +1. The time now is 06:01 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement