Hi

am trying to calculate variances in a spreadsheet and am running across a
problem with the formula returning the wrong value for the cell, for instance
returning a negative variance when in fact it is a positive variance. i'v
added another hidden column with the absolute value and would like a formula
that would show the same value (positive or negative) for the percentage as
what is displayed in that adjacent cell - any ideas?
--
wtrbb

Why not put ABS( ... ) around the formula you currently have, so it
will always show a positive value?

Hope this helps.

Pete

On Nov 30, 3:37 pm, wtrbb wrote:
Hi

am trying to calculate variances in a spreadsheet and am running across a
problem with the formula returning the wrong value for the cell, for instance
returning a negative variance when in fact it is a positive variance. i'v
added another hidden column with the absolute value and would like a formula
that would show the same value (positive or negative) for the percentage as
what is displayed in that adjacent cell - any ideas?
--
wtrbb

mmm - if it were only that simple! sometimes the variance is negative, though
- i'm running into the problem with the formulas that are computing negatives
to negatives and pos/neg (where the variance should be a positive one, but
returns a negative value instead)

(b4-c4)/c4
--
wtrbb


"Pete_UK" wrote:

Why not put ABS( ... ) around the formula you currently have, so it
will always show a positive value?

Hope this helps.

Pete

On Nov 30, 3:37 pm, wtrbb wrote:
Hi

am trying to calculate variances in a spreadsheet and am running across a
problem with the formula returning the wrong value for the cell, for instance
returning a negative variance when in fact it is a positive variance. i'v
added another hidden column with the absolute value and would like a formula
that would show the same value (positive or negative) for the percentage as
what is displayed in that adjacent cell - any ideas?
--
wtrbb

Please give examples of your data, your formulas, what you require as results and what you get instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"wtrbb" wrote in message ...
| mmm - if it were only that simple! sometimes the variance is negative, though
| - i'm running into the problem with the formulas that are computing negatives
| to negatives and pos/neg (where the variance should be a positive one, but
| returns a negative value instead)
|
| (b4-c4)/c4
| --
| wtrbb
|
|
| "Pete_UK" wrote:
|
| Why not put ABS( ... ) around the formula you currently have, so it
| will always show a positive value?
|
| Hope this helps.
|
| Pete
|
| On Nov 30, 3:37 pm, wtrbb wrote:
| Hi
|
| am trying to calculate variances in a spreadsheet and am running across a
| problem with the formula returning the wrong value for the cell, for instance
| returning a negative variance when in fact it is a positive variance. i'v
| added another hidden column with the absolute value and would like a formula
| that would show the same value (positive or negative) for the percentage as
| what is displayed in that adjacent cell - any ideas?
| --
| wtrbb
|
|

Actual Forecast absolute change % change
5,733 (2,600) 8,383.00 (322.4%)
=(a-b) =c/b

as you can see, i am getting a negative return for the % change, when its
actually a positive - we did better than forecasted - my signage should match
the signage of the number in column c

--
wtrbb


"Niek Otten" wrote:

Please give examples of your data, your formulas, what you require as results and what you get instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"wtrbb" wrote in message ...
| mmm - if it were only that simple! sometimes the variance is negative, though
| - i'm running into the problem with the formulas that are computing negatives
| to negatives and pos/neg (where the variance should be a positive one, but
| returns a negative value instead)
|
| (b4-c4)/c4
| --
| wtrbb
|
|
| "Pete_UK" wrote:
|
| Why not put ABS( ... ) around the formula you currently have, so it
| will always show a positive value?
|
| Hope this helps.
|
| Pete
|
| On Nov 30, 3:37 pm, wtrbb wrote:
| Hi
|
| am trying to calculate variances in a spreadsheet and am running across a
| problem with the formula returning the wrong value for the cell, for instance
| returning a negative variance when in fact it is a positive variance. i'v
| added another hidden column with the absolute value and would like a formula
| that would show the same value (positive or negative) for the percentage as
| what is displayed in that adjacent cell - any ideas?
| --
| wtrbb
|
|

Your problem is in trying to work out a percentage in this situation. If
your forecast was that you'd break even, then you'll be quoting an infinite
percentage whether you achieve a profit of £1 or £1 million.

If you merely want to ensure that the sign of your percentage is the same as
the sign of your change, then you could use =C1/ABC(B1), but it's a fairly
meaningless quantity.
--
David Biddulph

"wtrbb" wrote in message
...

Actual Forecast absolute change % change
5,733 (2,600) 8,383.00 (322.4%)
=(a-b) =c/b

as you can see, i am getting a negative return for the % change, when its
actually a positive - we did better than forecasted - my signage should
match
the signage of the number in column c

--
wtrbb


"Niek Otten" wrote:

Please give examples of your data, your formulas, what you require as
results and what you get instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"wtrbb" wrote in message
...
| mmm - if it were only that simple! sometimes the variance is negative,
though
| - i'm running into the problem with the formulas that are computing
negatives
| to negatives and pos/neg (where the variance should be a positive one,
but
| returns a negative value instead)
|
| (b4-c4)/c4
| --
| wtrbb
|
|
| "Pete_UK" wrote:
|
| Why not put ABS( ... ) around the formula you currently have, so it
| will always show a positive value?
|
| Hope this helps.
|
| Pete
|
| On Nov 30, 3:37 pm, wtrbb wrote:
| Hi
|
| am trying to calculate variances in a spreadsheet and am running
across a
| problem with the formula returning the wrong value for the cell,
for instance
| returning a negative variance when in fact it is a positive
variance. i'v
| added another hidden column with the absolute value and would like
a formula
| that would show the same value (positive or negative) for the
percentage as
| what is displayed in that adjacent cell - any ideas?
| --
| wtrbb
|
|

Well, if you insist.........

But a change from -2600 to 5733 really is a minus change, meaning a change of sign. You may have your own interpretations of
"positive" and "negative", like "better" or "worse", but there is no way in math you can change a sign other than "minussing" it

Example: let's say a is -100 and b = -200. Then the difference between the two is 100 (positive). If you say <my signage should
match the signage of the number in column c do you really mean this is a positive change (improvement)?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel





"wtrbb" wrote in message ...
|
| Actual Forecast absolute change % change
| 5,733 (2,600) 8,383.00 (322.4%)
| =(a-b) =c/b
|
| as you can see, i am getting a negative return for the % change, when its
| actually a positive - we did better than forecasted - my signage should match
| the signage of the number in column c
|
| --
| wtrbb
|
|
| "Niek Otten" wrote:
|
| Please give examples of your data, your formulas, what you require as results and what you get instead
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| "wtrbb" wrote in message ...
| | mmm - if it were only that simple! sometimes the variance is negative, though
| | - i'm running into the problem with the formulas that are computing negatives
| | to negatives and pos/neg (where the variance should be a positive one, but
| | returns a negative value instead)
| |
| | (b4-c4)/c4
| | --
| | wtrbb
| |
| |
| | "Pete_UK" wrote:
| |
| | Why not put ABS( ... ) around the formula you currently have, so it
| | will always show a positive value?
| |
| | Hope this helps.
| |
| | Pete
| |
| | On Nov 30, 3:37 pm, wtrbb wrote:
| | Hi
| |
| | am trying to calculate variances in a spreadsheet and am running across a
| | problem with the formula returning the wrong value for the cell, for instance
| | returning a negative variance when in fact it is a positive variance. i'v
| | added another hidden column with the absolute value and would like a formula
| | that would show the same value (positive or negative) for the percentage as
| | what is displayed in that adjacent cell - any ideas?
| | --
| | wtrbb
| |
| |
|
|
|

true, however, they want me to show in the spreadsheet whether our variance
was a positive or negative one -
--
wtrbb


"David Biddulph" wrote:

Your problem is in trying to work out a percentage in this situation. If
your forecast was that you'd break even, then you'll be quoting an infinite
percentage whether you achieve a profit of £1 or £1 million.

If you merely want to ensure that the sign of your percentage is the same as
the sign of your change, then you could use =C1/ABC(B1), but it's a fairly
meaningless quantity.
--
David Biddulph

"wtrbb" wrote in message
...

Actual Forecast absolute change % change
5,733 (2,600) 8,383.00 (322.4%)
=(a-b) =c/b

as you can see, i am getting a negative return for the % change, when its
actually a positive - we did better than forecasted - my signage should
match
the signage of the number in column c

--
wtrbb


"Niek Otten" wrote:

Please give examples of your data, your formulas, what you require as
results and what you get instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"wtrbb" wrote in message
...
| mmm - if it were only that simple! sometimes the variance is negative,
though
| - i'm running into the problem with the formulas that are computing
negatives
| to negatives and pos/neg (where the variance should be a positive one,
but
| returns a negative value instead)
|
| (b4-c4)/c4
| --
| wtrbb
|
|
| "Pete_UK" wrote:
|
| Why not put ABS( ... ) around the formula you currently have, so it
| will always show a positive value?
|
| Hope this helps.
|
| Pete
|
| On Nov 30, 3:37 pm, wtrbb wrote:
| Hi
|
| am trying to calculate variances in a spreadsheet and am running
across a
| problem with the formula returning the wrong value for the cell,
for instance
| returning a negative variance when in fact it is a positive
variance. i'v
| added another hidden column with the absolute value and would like
a formula
| that would show the same value (positive or negative) for the
percentage as
| what is displayed in that adjacent cell - any ideas?
| --
| wtrbb
|
|

I think David meant to put:

=C1/ABS(B1)

Pete

On Nov 30, 6:50 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Your problem is in trying to work out a percentage in this situation. If
your forecast was that you'd break even, then you'll be quoting an infinite
percentage whether you achieve a profit of £1 or £1 million.

If you merely want to ensure that the sign of your percentage is the same as
the sign of your change, then you could use =C1/ABC(B1), but it's a fairly
meaningless quantity.
--
David Biddulph

"wtrbb" wrote in message

...





Actual Forecast absolute change % change
5,733 (2,600) 8,383.00 (322.4%)
=(a-b) =c/b

as you can see, i am getting a negative return for the % change, when its
actually a positive - we did better than forecasted - my signage should
match
the signage of the number in column c

--
wtrbb

"Niek Otten" wrote:

Please give examples of your data, your formulas, what you require as
results and what you get instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"wtrbb" wrote in message
...
| mmm - if it were only that simple! sometimes the variance is negative,
though
| - i'm running into the problem with the formulas that are computing
negatives
| to negatives and pos/neg (where the variance should be a positive one,
but
| returns a negative value instead)
|
| (b4-c4)/c4
| --
| wtrbb
|
|
| "Pete_UK" wrote:
|
| Why not put ABS( ... ) around the formula you currently have, so it
| will always show a positive value?
|
| Hope this helps.
|
| Pete
|
| On Nov 30, 3:37 pm, wtrbb wrote:
| Hi
|
| am trying to calculate variances in a spreadsheet and am running
across a
| problem with the formula returning the wrong value for the cell,
for instance
| returning a negative variance when in fact it is a positive
variance. i'v
| added another hidden column with the absolute value and would like
a formula
| that would show the same value (positive or negative) for the
percentage as
| what is displayed in that adjacent cell - any ideas?
| --
| wtrbb
|
| - Hide quoted text -

- Show quoted text -

Yes, of course, you're right. It's been a long week. :-)
--
David Biddulph

"Pete_UK" wrote in message
...
I think David meant to put:

=C1/ABS(B1)

Pete

On Nov 30, 6:50 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Your problem is in trying to work out a percentage in this situation. If
your forecast was that you'd break even, then you'll be quoting an
infinite
percentage whether you achieve a profit of £1 or £1 million.

If you merely want to ensure that the sign of your percentage is the same
as
the sign of your change, then you could use =C1/ABC(B1), but it's a fairly
meaningless quantity.
--
David Biddulph

"wtrbb" wrote in message

...





Actual Forecast absolute change % change
5,733 (2,600) 8,383.00 (322.4%)
=(a-b) =c/b

as you can see, i am getting a negative return for the % change, when
its
actually a positive - we did better than forecasted - my signage should
match
the signage of the number in column c

--
wtrbb

"Niek Otten" wrote:

Please give examples of your data, your formulas, what you require as
results and what you get instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"wtrbb" wrote in message
...
| mmm - if it were only that simple! sometimes the variance is
negative,
though
| - i'm running into the problem with the formulas that are computing
negatives
| to negatives and pos/neg (where the variance should be a positive
one,
but
| returns a negative value instead)
|
| (b4-c4)/c4
| --
| wtrbb
|
|
| "Pete_UK" wrote:
|
| Why not put ABS( ... ) around the formula you currently have, so it
| will always show a positive value?
|
| Hope this helps.
|
| Pete
|
| On Nov 30, 3:37 pm, wtrbb wrote:
| Hi
|
| am trying to calculate variances in a spreadsheet and am running
across a
| problem with the formula returning the wrong value for the cell,
for instance
| returning a negative variance when in fact it is a positive
variance. i'v
| added another hidden column with the absolute value and would
like
a formula
| that would show the same value (positive or negative) for the
percentage as
| what is displayed in that adjacent cell - any ideas?
| --
| wtrbb
|
| - Hide quoted text -

- Show quoted text -