# IF And statment work in Access not in Excel

Microsoft Excel 2007
January 28, 2010 at 06:19:02
Specs: Windows 7
 I have a IF AND statement which works in Access as a SQL query but I can not get it to work in Excel as a formula.If X is yesterday and Y is today then I need the % change, I have.Y\X - 1Where X and Y can be 0, further ifY\X=1 %change=0Y=X=0 %change=0X>Y %change= - #X=0 %change=YY=0 %change= - XIn Access I haveIIF(X<>0 AND Y\X<>0,Y\X-1, Y-X) Which seems to work I get no error msg. and in testing gives the correct answers.In Excel I haveIF(AND(X<>0, Y\X<>0), Y\X - 1, Y-X)Which gives me the error: dividing by zero.Is there a way to make this work in Excel or can you see a better way to do this.Thank youI

See More: IF And statment work in Access not in Excel

#1
January 28, 2010 at 07:08:59
 Hi,I am not sure if I have all your logic requirements correct!Give this a try=IF(AND(B16=0,B17=0),0,IF(B16=0,B17,IF(B17=0,-B16,IF(B16>B17,-((B16/B17)-1)*100,((B16/B17)-1)*100))))where B16 is X and B17 is YRegards

Report •

#2
January 28, 2010 at 13:38:28
 Thank you, that worked with a little modification.

Report •

Related Solutions