Variance (percentage) between two negative numbers

[pmmpinho]

I am analyzing the variance between the projected EBIT and the actual EBIT for some projects.

When both numbers are positive is quite simple. Example: projected=10,000 and actual=5,000 (variance=50%)

The problem is when both numbers are negative:

projected = -2,241 and actual=-2,888

Which formula should I use to calculate this variance?

 

[Luke M]

Wouldn't it still evaluate out to be:

Actual / Goal = Variance

-2888/-2241 = 129%


If the goal is for actual to be higher in value (ie, less negative), than you can swap the order to be:

Goal / Actual = Variance

-2241/-2888 = 78%

In which case, as actual decreases in value, the variance will also decrease.

 

[Brant]

Hi,


I believe it would be the same formula.

Variance = (Actual/Projected)-1


Projected = 10,000, Actual = 5,000

Variance = (10,000/5,000)-1 = -50.00%


Projected = -10,000, Actual = -5,000

Variance = (-10,000/-5,000)-1 = -50.00%


Brant

 

[PSG]

I believe your formula should be as follows:


Variance = (Actual-Projected)/Actual


(-2888-(-2241))/-2888 = 22%


Let me know if you need any clarification on this.

 

[Montrey]

=IF((J8-J9)/J9<=0,((J8-J9)/J9)*-1,(J8-J9)/J9)

 

[Luke M]

@Montrey

I think you could reduce your formula to just:

=ABS((J8-J9)/J9)

but I still like it. =)

 

[SirJB7]

@Luke M

Hi!

Following oldchippy's guidelines:

=ABS(J8/J9-1)

30% less of characters...

Regards!

 

[Montrey]

Fine! you win! BAH HUMBUG


JK I <3

 

[Ian Digby]

I think it depends on the accounting sense of what you are trying to convey. So with income, less is bad, thus a negative variance; with expenditure, less is good, thus a positive variance. I can't think of a mathematical way to encompass this logic in a simple formula so I think you need to preface your formula accordingly.
For example, with expenditure, the Excel version would be:

=IF(Act>Bud,-(ABS(Bud-Act)/ABS(Bud)),ABS(Bud-Act)/ABS(Bud))

Then of course you have to account for division by zero, so:

=IF(Bud=0,0,IF(Act>Bud,-(ABS(Bud-Act)/ABS(Bud)),ABS(Bud-Act)/ABS(Bud)))

It's a fascinating subject where reality complicates maths a great deal!

 

[SirJB7]

@Ian Digby
Hi!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue...

If you're posting to ask something about the subject, would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Now, if you only wanted to provide an alternative I'd recommend you to focus your efforts on actual and active threads instead of those older to which the OP probably would never return. Thank you.

Regards!

PS: Please don't answer here at this thread.