Percent Change Between Two Percents but Have One that's 0.00% and 0.00%

[rkmc783]

My clients have asked for a report that tracks the % change in error rates from one month to the next for their regions. They have specifically asked that if an error decreases, the % should read with a " - " for easier viewing.

For example, Region 1 went from a 9.09% error rate in February to 8.33% error rate in March. The percent change should look like "-8.36%."

The previous months error rate would be in the B column, the current months C column, and the change D. So for the example it would be B2 = 9.09%, C2= 8.33%, and D2 = -8.36%.

I entered the formula =IF((B2=0),1,((B2-C2)/(B2)*-1)) in the D cells. This works fine for regions with an increase from 0.00% or decrease to 0.00%. Example 7.14% error rate to 0.00% shows as -100.00% and vice versa. It also works fine if the error rate was the same, but only if there was an error rate. Example 50.00% to 50.00% returns 0.00%.

The problem I face is when there is 0.00% error rate for the previous month, and 0.00% error rate for the current month. The formula returns 100.00%. Example Region No4 had 0.00% (B2) in February, 0.00% (C2) in March and the change shows 100.00% (D2) when using the above formula.

I came up with this formula =IF((OR(B2=0,(AND( B2+C2=0)))),0,((B2-C2)/(B2)*-1).Using the Region No4 example, it would return 0.00% in D2, which is exactly what the client wants.

It solves the issue for that specific row, but it will not work for regions where there is a change to 0.00% or from 0.00%. If that scenario occurs, it shows the change as 0.00%, which is not what my client wants. Example Region No8 went from 0.00% in February (B2) to 16.67% in March (C2) and the change shows as 0.00% (D2).

Is there a way I can make the two formulas work together? I've been playing around with it for the better part of an hour now, and nothing I've tried works. Any ideas? This might be easier solved using VBA, however, I'm very inexperienced with VBA and was hoping for a worksheet formula solution.

I would just enter that specific formula for regions where 0.00% and 0.00% are present, but there are over 400 regions. I'm not wanting to look at all the changes, just want to insert the formula. I've even resorted to filtering for 0.00% and 0.00% and copying the second formula to those specific cells, then copying the first formula to the remaining cells. It solves my problem, but it's about solving the original issue at this point.

Thanks for any help anyone can give me on this.

 

[Scott Huish]

There's a couple things wrong with your formula, the first is a whole bunch of extraneous parentheses. Your AND condition only has one condition in it. I think you mean OR on both conditions. However, if B2 was 7 and C2 was -7, that would add to 0, but I don't think it's what you're looking for. I think you're looking to see if both are 0 or B2 is 0.

Try this:
=IF(OR(B2=0,AND(B2=0,C2=0)),0,-((B2-C2)/B2))

 

[rkmc783]

Thanks Scott, that's such a cleaner formula than the original one, it does everything I need it to do but the 0.00% to 0.00%, meaning no change in the regions error rate when it was 0.00% the previous month and 0.00% the current month. It still shows that as 100% change. But this gives me a much cleaner formula to play with now. Thank you!

 

[rkmc783]

Scott, that was exactly what I needed to get it on the right track. The formula that provides all the desired results is =IF((B2+C2=0),0,(IF(OR(B2=0,AND(B2=0,C2=0)),1,-((B2-C2)/B2))))