Percent to Budget Calculation with Negative Numbers

Good morning. I am stumped on a calculation I need to run and the ABS function doesn't seem to correctly calculate each time.

I've attached a sample file showing Actual, Budget, Variance and the % to budget calculation we currently use highlighted in green. I took a stab at it to make one formula instead of using several to get the right result (highlighted in yellow) and it's still wrong!

This is a % to budget EBITDA calculation and we expected some of our offices to show negative EBITDA for Q1. Some, like Unit A, were budgeted for a negative EBITDA number and still came in at negative EBITDA although much better than e expected. Unit I was budgeted for positive EBITDA but came in at a major negative on the actual. That particular unit has to use a different formula just to get the correct result.

I hope this makes sense. I'm trying to find an all-in-one calculation that will do these and make them correct! I appreciate any help that I can get!

Sorry, I think I miscommunicated what I'm trying to do. In the "original" column, there are two different formulas entered to arrive at the right answer. The "My version" column has my attempt to create one formula instead of using two.

Take Unit A, the current calc in the column is E3/ABS(D3)+100% . Since that was a negative budget number and a negative actual that is better than budget, I have to add 100% to it to get the true % to budget

On the flip side is Unit I. That one is E11/ABS(D11) with no 100% adder. Since their actual was less than their positive budget number, it calculates differently to arrive at the right answer.

Basically, I'm trying to devise one formula to arrive at the answer instead of two.

Check and see if it is what you want...