% calculations

[ChristyB]

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?

[Roger Govier]

Hi Christy

Assuming your real data is in A1 and your budget in B1 then enter in C1
=IF(B1<1,"Not Applicable",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 Govier


"ChristyB" <ChristyB@discussions.microsoft.com> wrote in message
news:4216C899-6543-484A-91FD-0DB372B4DDD1@microsoft.com...
>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?

[joeu2004@hotmail.com]

"Roger Govier" wrote:
> Assuming your real data is in A1 and your budget in B1
> then enter in C1 =IF(B1<1,"Not Applicable",A1/B1)

I think the condition should be "B1 <= 0".

[Bob Phillips]

=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)

"ChristyB" <ChristyB@discussions.microsoft.com> wrote in message
news:4216C899-6543-484A-91FD-0DB372B4DDD1@microsoft.com...
> 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?

[joeu2004@hotmail.com]

"ChristyB" wrote:
> 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?

As you probably, mathematically there is no answer. But that
does not stop people from wanting a "reasonable" 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
"typical" budget item? (Most people prefer a blank or a
number, not words like "not applicable" or "NA".)

Some formulations of each:

=if(B1<=0, "", A1/B1 - 1)
=if(B1<=0, 100%, A1/B1 - 1)
=if(B1<=0, A1, A1/B1 - 1)
=if(B1<=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 "<=" is called "defensive programming". You
might prefer "B1=0", especially if you know the budget
value is never less than zero ;-).