Percentage Variation - when values are zero

[LinLin]

Hi Everyone

I have this data:

<Actuals> <Budget> <Variance> <As %>
100 200 100 -50% (ie 50% below budget)
100 100 0 0%
200 100 -100 100% (100% above budget)
100 0 -100 100%
0 100 100 -100%
200 100 -100 100%

Is there one formula I can use to calculate the % variation which will
accept zero as an Actual, a Budget, or even both being zero?
(And of course also if Actual and/or Budget is negative too).

Hope my logic is correct too!

(I saw some solutions a while back by John C (johnc@stateofdenial) a while
back in the archives but it didn't cover cases with zero).

Many thanks!

 

[Gary''s Student]

With your data in cols A,B,C:

=-C1/IF(B1=0,-C1,B1) format as % and copy down

 

[Teethless mama]

=-C2/((B2=0)*A2+B2)

 

[LinLin]

Hello Teethless and gary's students

That formula will always run into problems with neg values.

So imagine my original data was for Income.
I earned 100, I expected to earn 200, so I under earnt 100 (50% below
budget, which works with the formula)

BUT
If my earnings went negative (I had to give all the income back!) AND I
expected to return earnings so I had a negatuve budget too, this is my
situation:
earned -100, expected to earn -200, variation is -100 AND I have OVERearned,
so my %variance is actually +100%

So perhaps the formula has to do some comparison to see if a number is <0, 0
or >0, and then do its thing, rather than assign a minus to the formula
automatically?

It's been very helpful so far, as you can see, it's a hairy one!

thanks!

 

[joeu2004]

BUT If my earnings went negative (I had to give
all the income back!)  AND I expected to return
earnings so I had a negatuve budget too, this is
my situation: earned -100, expected to earn -200,
variation is -100 AND I have OVERearned,
so my %variance is actually +100%

(No, your actual is 50% more than budget.)

I think the following modification would do what you want:

=-C2/ABS(A2*(B2=0)+B2)

where C is the variance, A is the actual, and B is the budgeted (aka
expected). See the additional cases below.

Actual Budget Variance Pct Below Budget
100 200 100 -50.00%
100 100 0 0.00%
200 100 -100 100.00%
100 0 -100 100.00%
0 100 100 -100.00%
200 100 -100 100.00%
-100 -200 -100 50.00%
100 -100 -200 200.00%

However, I quibble with your definition of variance. You are
computing it as =B2-A2. I would compute as as =A2-B2. Thus, variance
has the same sign and interpretation as Pct, namely: an amount above
(+) or below (-) budget.

In that case, Pct can be computed by changing =-C2/... to =C2/....

 

[joeu2004]

Errata....

Actual  Budget  Variance        Pct Below Budget

The last column should be "Pct of Budget", where negative means "%
less" and positive means "% more", as you had it.

 

[LinLin]

Hello Joeu

Thank you for that.

Yes, it was pointed out to me that I had my variance around the wrong way :-(
Sorry about that, and everyone who contributed.
I'll have a go at that. (More feedback to come....)

 

[LinLin]

That was AWESOME - thanks for that Joeu2004!

cheerio
LinLin