Formula for %Difference w/ two error scenarios

MPatrowic

New Member
Joined
Feb 19, 2019
Messages
2
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-
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top