Just to add a little to the knowledgeable replies you got already:
Display something like "Not Defined"
I bet they won't dare to correct you
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
|I agree with your point there, however, the decision makers I am creating
| this for do not agree. they want to see the % goal attainment whether it is
| positive or negative. this is why i'm stuck on trying to find a formula i
| can imbed that will work.
|
| thank you for your quick response however, i do appreciate the help.
| smw
|
| "David Biddulph" wrote:
|
| > The advice is that percentages are pretty meaningless in that situation. If
| > you make a small adjustment and think about a case where the goal is to
| > break even; how would you define your percentage there?
| > In the case you quoted, if you wanted you could look at the difference
| > between target and actual, and take that as a percentage of the magnitude of
| > the target, so ($100 - (-$100))/abs(-$100) would give a 200% improvement,
| > ((-$60) - (-$100))/abs(-$100) would be a 40% improvement, breaking even
| > would be a 100% improvement, and achieving the target would give a 0%
| > improvement, but (as I said at the start) I don't think that percentages are
| > usually very useful in such situations..
| > --
| > David Biddulph
| >
| > | > >I want to write a formula that calculates the percent attainment of a goal
| > > i.e. profit goal. If i have a goal of $-100 and actually attain $100
| > > (positive) profit. What is the formula to write such that the resulting %
| > > does not contain a negative? Additionally, would like the same formula to
| > > take into account if goal is $-100 and actually attain $-60, the %
| > > attainment
| > > should be positive.
| > >
| > > any advise?
| >
| >
| >
