On Jul 18, 11:34 pm, marc...@excite.com wrote:
> can you tell me what the "SIGN" and the "ABS"
> do in this formula.

SIGN() returns -1, 0 or 1 for negative, zero and positive numbers.
Note that 1 is 100%.

ABS() returns the magnitude or "absolute value" of a number; that is,
the number without its sign. For example, ABS(-100) is 100, and
ABS(100) is also 100.

The formula works the way we think of percentage change intuitively.
The gain or loss is determined by the direction of change. The
percentage is determined by the ratio of change over the magnitude of
the base value.

ERRATA.... I wrote:
> 2. A change from 50 to 0 will be a 100% loss (100%),
> and from 50 to -50 will be a 200% loss (-200%).

Obvious typo: I should have written "(-100%)".


----- original posting -----

On Jul 18, 11:34*pm, marc...@excite.com wrote:
> wow!!! *impressed, works great.
> can you tell me what the "SIGN" and the "ABS" do in this formula.
>
> thanks,
>
> On Jul 18, 10:52*pm, joeu2004 <joeu2...@hotmail.com> wrote:
>
>
>
> > On Jul 18, 9:50*pm, marc...@excite.com wrote:
>
> > > Thanks, I am thinking maybe this is the way excel
> > > calculates!!!!
>
> > Excel does not calculate any particular "way". *You tell Excel how you
> > want something calculated, and Excel does it. *Whether or not the
> > calculation makes sense is entirely up to you.
>
> > > (A) $3799.95 is sales for June 2008
> > > (B) $-210 is sales for June 2007 [....]
> > > So in this case I would like to know in June 2008
> > > how many +/- % is it.
>
> > Okay, you do indeed have the right idea. *If the more-recent number
> > (2008) is in A3 and the older number (2007) is in B3, I think the
> > following formula will give you the result you desire:
>
> > =IF(B3=0, SIGN(A3), (A3-B3)/ABS(B3))
>
> > If the old number was zero, any positive change will be treated as
> > 100%, and any negative change will be treated as -100%. *This is an
> > arbitrary choice. *There is no right answer.
>
> > Otherwise:
>
> > 1. A change from -50 to 0 will be a 100% gain, and from -50 to 50 will
> > be a 200% change. *Likewise for 50 to 100 and 50 to 150.
>
> > 2. A change from 50 to 0 will be a 100% loss (100%), and from 50 to
> > -50 will be a 200% loss (-200%). *Likewise for -50 to -100 and -50 to
> > -150.
>
> > In your case, a change from -210 to 3800 about a 1910% gain.- Hide quoted text -
>
> - Show quoted text -