|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 19th, 2009, 09:36 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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% 
|
|
#2
November 19th, 2009, 11:09 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mutiple IF(AND) too many conditions
No, you are not on the right track.
AND(A250) is just the same as (A250), as you haven't told Excel what you want to AND with (A250). 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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-C25/B25),IF(AND(B250),(C25/B25)))))))) to =IF(B25=0,IF(A250,-1,IF(A250,1,0)),IF(C25=0,IF(B250,C25/B25,IF(B250,-C25/B25,IF(C25=0,IF(B250,-C25/B25,IF(B250,C25/B25))))))) Is that what you were trying to achieve? -- David Biddulph "rgl2sa" wrote in message news  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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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%
|
|
#3
November 19th, 2009, 12:41 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Mutiple IF(AND) too many conditions
In D2 try the below formula.....which give the (current results)
=IF(AND(A2=C2,A20),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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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%
|
|
#4
November 19th, 2009, 12:43 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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,A20),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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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%
|
|
#5
November 19th, 2009, 12:49 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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%
|
|
#6
November 19th, 2009, 07:20 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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,A20),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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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%
|
|
#7
November 19th, 2009, 07:23 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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(A250) is just the same as (A250), as you haven't told Excel what you want to AND with (A250). 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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-C25/B25),IF(AND(B250),(C25/B25)))))))) to =IF(B25=0,IF(A250,-1,IF(A250,1,0)),IF(C25=0,IF(B250,C25/B25,IF(B250,-C25/B25,IF(C25=0,IF(B250,-C25/B25,IF(B250,C25/B25))))))) Is that what you were trying to achieve? -- David Biddulph "rgl2sa" wrote in message news 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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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% .
|
|
#8
November 19th, 2009, 07:28 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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%
|
|
#9
November 19th, 2009, 08:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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,A20),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(A250),-1,IF(AND(A250),1,0)),IF(C25=0,IF(AND(B250),(C25/B25),IF(AND(B250),(-C25/B25),IF(C25=0,IF(AND(B250),(-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%
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
