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 - 1

Where X and Y can be 0, further ifY\X=1 %change=0

Y=X=0 %change=0

X>Y %change= - #

X=0 %change=Y

Y=0 %change= - XIn Access I have

IIF(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 have

IF(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 you

I

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 Y

Regards

Thank you, that worked with a little modification.

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History