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%

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%

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%

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%

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%

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%

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%





.

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%

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%