software

I am looking to compare actuals to budgeted amounts.
The actuals are real $. The amount budgeted was $0.00. I need the
percentage over. What formula can I use to get the % over budget in Excel?

Hi Christy

Assuming your real data is in A1 and your budget in B1 then enter in C1
=IF(B1lt;1,quot;Not Applicablequot;,A1/B1)
Format Cell C1 as Percentage, copy down column C as far as required
If the Budget is 0, you cannot have a percentage increase, as this would
be infinity (and Excel would throw up a #DIV/0 error), hence the If test
at the beginning of the formula.

--
Regards

Roger Govierquot;ChristyBquot; gt; wrote in message
...
gt;I am looking to compare actuals to budgeted amounts.
gt; The actuals are real $. The amount budgeted was $0.00. I need the
gt; percentage over. What formula can I use to get the % over budget in
gt; Excel?
=actual/budget

formatted as percentage, or maybe

=actual/budget-1

to get just the percentage over

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;ChristyBquot; gt; wrote in message
...
gt; I am looking to compare actuals to budgeted amounts.
gt; The actuals are real $. The amount budgeted was $0.00. I need the
gt; percentage over. What formula can I use to get the % over budget in
Excel?
quot;Roger Govierquot; wrote:
gt; Assuming your real data is in A1 and your budget in B1
gt; then enter in C1 =IF(B1lt;1,quot;Not Applicablequot;,A1/B1)

I think the condition should be quot;B1 lt;= 0quot;.

quot;ChristyBquot; wrote:
gt; The actuals are real $. The amount budgeted was $0.00.
gt; I need the percentage over. What formula can I use to get
gt; the % over budget in Excel?

As you probably, mathematically there is no answer. But that
does not stop people from wanting a quot;reasonablequot; result to
put into presentations.

The choice is arbitrary and subjective. There is no right or
wrong. Do you want a blank? Do you want a constant, like
100%? Or do you want a percentage that scales by the
amount commensurate with the size of the budget or
quot;typicalquot; budget item? (Most people prefer a blank or a
number, not words like quot;not applicablequot; or quot;NAquot;.)

Some formulations of each:

=if(B1lt;=0, quot;quot;, A1/B1 - 1)
=if(B1lt;=0, 100%, A1/B1 - 1)
=if(B1lt;=0, A1, A1/B1 - 1)
=if(B1lt;=0, round(A1/1000,0), A1/B1 - 1)

The latter is a multiple of 100% times the rounded number
of $1000 increments. Thus, 100% if A1 is $500-$1499
and 200% if A1 is $1500-$2499.

Note: Using quot;lt;=quot; is called quot;defensive programmingquot;. You
might prefer quot;B1=0quot;, especially if you know the budget
value is never less than zero ;-).