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?

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

"smw2340" wrote in message
...
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?

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

"smw2340" wrote in message
...
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?

David has summarized the issue very well. My bet is the only solution to
your problem is to have the decision makers tell you what they want. Give
them the same examples you gave us. Ask them whay they want to see in each
situation. Then program it.

Regards,
Fred.

"smw2340" wrote in message
...
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

"smw2340" wrote in message
...
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?

So ask your "decision makers" to specify unambiguously what they want. If
they can't specify the question, they can't expect you to provide an answer.
--
David Biddulph

"smw2340" wrote in message
...
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

"smw2340" wrote in message
...
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?

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

"smw2340" wrote in message ...
|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
|
| "smw2340" wrote in message
| ...
| 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?
|
|
|

thank you all for the input. they'll get what they get until they can come
up with a better solution!

"Niek Otten" wrote:

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

"smw2340" wrote in message ...
|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
|
| "smw2340" wrote in message
| ...
| 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?
|
|
|