Share via

Need Budget Variance to Show as Positive or Negative

Anonymous
2015-10-25T19:25:13+00:00

Hi All,

I am trying to figure out a way for my variance to budget formula to show up as a positive or negative percentage. 

Sometimes actual is over the budgeted amount, and sometimes it is under.  How do I write the formula so that it automatically applies the correct calculation?  Examples:

If Variance is Over Budget

Budget =J7

Actual = F13

Formula is =(F13-J7)/J7

I would also like the over budget variance to display in red as a negative number: -##.#%

If Variance is Under Budget

Because the budget is favorable to actual, the formula will need to be different (I think, because it's a decrease versus what was budgeted)?

Budget for next item is J8

Actual for next Item is F14

=(J8-F14)/J8

In this case, I would like the under budget variance to display in the standard blue color set up for the rest of the worksheet to show positive numbers:  ##.#%

Thank you so much for your help.

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

Answer accepted by question author

  1. Anonymous
    2015-10-25T23:56:22+00:00

    % Budget Variance =  Difference/budgeted amount

    As you state your preference, you would want   (Budget-Actual)/Budget 

    this would give a negative if the Actual is greater than the budget and positive if the Actual is less than the budget.    It would return a proportion.  If formatted as a percent, then it will display a percent.  

    formatting has 4 parts

    positive format;negative format;format for zero;Text

    so you can design your format to display what you want.

    If I select a cell, right click and select format cells, then choose number tab and then custom  and put in this format

    0.0%;[Red]-0.0%;0%;

    then it should approach what you describe.  (make the normal font color of the cell the blue you want to see for positive).    

    --

    Regards,

    Tom Ogilvy

    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-08-25T23:30:42+00:00

    I created a formula, it’s pretty long. G8 represents the cell for budget, F8 represents the cell for actual.

    =IFS(SIGN(F8)+SIGN(G8)=2,(F8-G8)/G8,SIGN(G8)-SIGN(F8)=2,(F8-G8)/G8,SIGN(G8)-SIGN(F8)=-2,(-1)*((F8-G8)/G8),SIGN(G8)-SIGN(F8)=0,(-1)*((F8-G8)/G8))

    If you wanted to test it you could paste the formula in the cell in which you want to populate with the variance and then you Find and replace all G8’s with the cell that holds the budget amount on your current worksheet and do the same for F8!!

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2015-10-25T22:32:53+00:00

    Hi Tom,

    Thank you so much for your answer. I'm wondering if I'm over-complicating this -- because the formula for the variance should be consistent, and it's the formatting that should be adjusted.

    So, if formula is:

    Budget =J7

    Actual = F13

    Formula is =(F13-J7)/J7

    Then I'm thinking that I want the answer to display differently than how it appears.  When I choose percent to format the numbers, budget overruns display as a positive number. 

    I would like them to show up in Red as -##.##% (with a minus sign in front of the percent).

    Numbers that are favorable to budget show up as negative numbers, but I would like them to display a positive number in the same color as other numbers in the spreadsheet.

    I tried using a custom number format, but it makes everything the same color (red). 

    Thank you again.

    Ann

    0 comments
  3. Anonymous
    2015-10-25T22:04:05+00:00

    Thank you Tom.  I was wondering if there is a way to use a conditional statement?  Or perhaps I am over-thinking this and the formula is the same for both (Actual -Budget/Budget) and it's the formatting that's the issue?

    0 comments
  4. Anonymous
    2015-10-25T21:53:07+00:00

    If you use one formula with the budgeted amount first and subtract the actual amount, your first case will result in a negative number and negative value formatting will be applied - use the format you describe above as your negative number formatting.  The second case will result in a positive number and positive number formatting will be applied.  I assume this will be the blue color you describe.  

    --

    Regards,

    Tom Ogilvy

    0 comments