Formula to calculate % variance between four columns

[JDeMaro22]

Hello,

I have four columns of data that I need to write a variance percentage formula for but I can't figure one that that returns the correct values. I need it to give me the Total Variance % between April-May 2022 vs April-May 2023 but when it divided by any columns with 0 I get an error. I attached my example as a reference.

Thank you very much,

Joshua

[HansV]

Mathematically speaking, you can't calculate variance from zero to a non-zero value: it'd be infinitely large. But if you (rather arbitrarily) want to use 100%, change the formula to

=IF(AVERAGE(B2:C2)=0, IF(AVERAGE(D2:E2)=0, 0, 1), AVERAGE(D2:E2)/AVERAGE(B2:C2)-1)

[JDeMaro22]

I can't get the %s to look the way I want. For example if I use this formula for 2022 at 2 and 2023 at 6 it comes up with a positive 200% but shouldn't that be 300%? And if we have 2022 at 0 and 2023 at 3 wouldn't that be 300% as well?

[HansV]

If 2022 is 2 and 2023 is 6, the increase is 6-2 = 4, and that is 200% of 2.
300% would not be the increase from 2 to 6, but the ratio of 6 to 2.

As I mentioned, you cannot really calculate the increase from 0 to a non-zero value. It is infinitely large. I chose 100% as an arbitrary replacement, but you can make it anything: 50% or 2000% or 2345678%, or even 300%.