Percentage Variation - when values are zero
 

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!

Answer: Percentage Variation - when values are zero
 

Percentage Variation - when values are zero
 

With your data in cols A,B,C:

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

--
Gary''s Student - gsnu200816

Percentage Variation - when values are zero
 

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


"LinLin" wrote:

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!

Percentage Variation - when values are zero
 

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!

"Teethless mama" wrote:

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


"LinLin" wrote:

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!

Percentage Variation - when values are zero
 

On Nov 30, 2:51*pm, LinLin wrote:
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/....

Percentage Variation - when values are zero
 

Errata....

On Nov 30, 8:27*pm, I wrote:
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.

Percentage Variation - when values are zero
 

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....)


"joeu2004" wrote:

Errata....

On Nov 30, 8:27 pm, I wrote:
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.

Percentage Variation - when values are zero
 

That was AWESOME - thanks for that Joeu2004!

cheerio
LinLin

"joeu2004" wrote:

Errata....

On Nov 30, 8:27 pm, I wrote:
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.