Need to calculate percentage with some variables

[Cristin]

Hello,

I'm trying to calculate the % of YTD Actual # compared to the Annual budget
#. In some cases it's easy:

YTD Actual = $10,000, Annual budget = $20,000, so 10000/20000 = 0.5
(formatted to a percentage = 50%)

However, in cases where we budgeted a loss, but have actually seen a profit,
i'm stumped:

YTD Actual = $12,000, Annual budget = <$9,000>: WHAT IS THE FORMULA FOR THIS?

Any help is much appreciated.

 

[Gaurav]

what do you want in such a case? that would decide the formula.

 

[Cristin]

Well that's the kicker

When Actual= and budget= Result=
12,000 -9,000 -133.33%
2,500 0 #DIV/0!
2,500 2,000 125.00% (this one makes sense)

what does that say, really?

 

[Ashish Mathur]

Hi,

This is more a mathematical than an Excel question. It honestly does not
make sense to compute a %age in such a scenario.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Fred Smith]

I would use the following formula:

=(Actual-Budget)/ABS(Budget)

Regards,
Fred.