Share via

Actual vs Budget on excel as a positive or negative

Anonymous
2013-03-21T12:29:44+00:00

I need to add a formula to excel to show variance on actual versus budget and have it show as a positive or negative. 

Actual is approx. 120000 vs budget of 204000

Using =(budget/actual)-1 I'm getting an answer of 69%, but I need to show this as a negative as actual is lower than budget, but can't work out how to do this?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-03-21T13:02:11+00:00

    If you use =1-budget/actual with your numbers (120,000 and 204,000) you get -70%.

    This is the number you asked for but is it meaningful?

    Suppose actual = 50 and budget = 200 (or 50,000 and 200,000) then the formula gives you -300%

    But what does this mean?

    However, with actual = 50 and budget = 200 , =actual/budget-1 gives -75% tell us we have 75% of the budget left - seem more meaningful to me

    With 120,000 and 204,000 it returns -41.2%. I have 41.2% of my budget unspent

    And if actual = 500,000 while budget = 200,000 it give +50% -- we have overspent the buget by this amount

    best wishes

    6 people found this answer helpful.
    0 comments