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!

With your data in cols A,B,C:

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

--
Gary''s Student - gsnu200816

=-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!

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!

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

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.

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.

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.