Formula for %Difference w/ two error scenarios

[MPatrowic]

I can't quite figure out the formula for the following scenario - We are looking at counts and adjustments so we want to see positive and negative % differences. The problem is if the book quantity is 0 and the count quantity is 0 and the book quantity is 0 and the count quantity is anything but 0. I am able to get a formula to work for each situation separately, but not quite sure how to do it together.

The formula I started with was =iferror(L1/C1,0). That works for 0 and 0. If I change the 0 in the formula to 1, that works for a positive count vs. 0 book.


A (Book Quantity) B (Count Quantity) C(Count Diff) D(% Diff)
0.00 0.00 0.00 0% =IFERROR(C1/A1,0)
0.00 100.00 100.00 100.% =IFERROR(C1/A1,1)


Thanks-

 

[Gerald Higgins]

Hi, welcome to the board.

Your request is a bit confusing, but I think I understand what you mean.

=if(and(c1=0,a1=0),0,if(and(c1<>0,a1=0),1,c1/a1))

This probably seems a bit long winded, but I think it might be better than relying on the IFERROR function, as there might possibly be other types of errors that occur, that you might want to handle differently.

 

[MPatrowic]

Thank you so much, that worked perfectly.