+ Reply to Thread
Results 1 to 8 of 8

How to show negative in cell?

  1. #1
    Brett Romero
    Guest

    How to show negative in cell?

    If I divide two negative numeric cells and put the result into a percentage
    cell, it will positive, even if the change is negative. For example:

    A B
    1 -6249 -5810

    A1/B1 = 107.56%

    The change is moving from B to A. That means I should have a negative
    change. How can I show that?

    Thanks,
    Brett



  2. #2
    Gilles Desjardins
    Guest

    Re: How to show negative in cell?

    Just multiply it by -1. =(A1/B1)*-1

    Gilles
    "Brett Romero" <[email protected]> wrote in message
    news:[email protected]...
    > If I divide two negative numeric cells and put the result into a
    > percentage cell, it will positive, even if the change is negative. For
    > example:
    >
    > A B
    > 1 -6249 -5810
    >
    > A1/B1 = 107.56%
    >
    > The change is moving from B to A. That means I should have a negative
    > change. How can I show that?
    >
    > Thanks,
    > Brett
    >




  3. #3
    Harlan Grove
    Guest

    Re: How to show negative in cell?

    "Brett Romero" <[email protected]> wrote...
    >If I divide two negative numeric cells and put the result into a percentage
    >cell, it will positive, even if the change is negative. For example:
    >
    > A B
    >1 -6249 -5810
    >
    >A1/B1 = 107.56%
    >
    >The change is moving from B to A. That means I should have a negative
    >change. How can I show that?


    If you want to show this as a negative, no one will stop you, but it's
    *correctly* appearing as a positive. Percentages > 100% imply increase in an
    absolute sense, while those > 0% but < 100% imply decrease (and < 0% means
    sign change, in which case the absolute value has no interpretive value). If
    you start off with a negative value and end with a larger negative value,
    you have *INCREASED* the negative value, not decreased it.

    Percentages are multiplicative concepts, not additive ones (this is a common
    misunderstanding), so additive change should be considered irrelevant.

    If you want to register negative change, use addition: =A1-B1. If you want
    to measure percentage (relative) change, then you're faced with the
    ambiguity of ratios: the ratio has problematic interpretive value unless you
    know the sign of the initial value. In most financial publications, ratios
    with either or both terms negative are often shows as NMF - not meaningful.



  4. #4
    Brett Romero
    Guest

    Re: How to show negative in cell?


    "Harlan Grove" <[email protected]> wrote in message
    news:[email protected]...
    > "Brett Romero" <[email protected]> wrote...
    >>If I divide two negative numeric cells and put the result into a
    >>percentage cell, it will positive, even if the change is negative. For
    >>example:
    >>
    >> A B
    >>1 -6249 -5810
    >>
    >>A1/B1 = 107.56%
    >>
    >>The change is moving from B to A. That means I should have a negative
    >>change. How can I show that?

    >
    > If you want to show this as a negative, no one will stop you, but it's
    > *correctly* appearing as a positive. Percentages > 100% imply increase in
    > an absolute sense, while those > 0% but < 100% imply decrease (and < 0%
    > means sign change, in which case the absolute value has no interpretive
    > value). If you start off with a negative value and end with a larger
    > negative value, you have *INCREASED* the negative value, not decreased it.
    >
    > Percentages are multiplicative concepts, not additive ones (this is a
    > common misunderstanding), so additive change should be considered
    > irrelevant.
    >
    > If you want to register negative change, use addition: =A1-B1. If you want
    > to measure percentage (relative) change, then you're faced with the
    > ambiguity of ratios: the ratio has problematic interpretive value unless
    > you know the sign of the initial value. In most financial publications,
    > ratios with either or both terms negative are often shows as NMF - not
    > meaningful.

    If both numbers are negative and the change is increasing in a negative
    direction (as above), how can I flag the resulting cell as red? If the A1
    had been 5000, the resulting cell would remain black.

    Thanks,
    Brett



  5. #5
    Harlan Grove
    Guest

    Re: How to show negative in cell?

    "Brett Romero" <[email protected]> wrote...
    ....
    >If both numbers are negative and the change is increasing in a negative
    >direction (as above), how can I flag the resulting cell as red? If the A1
    >had been 5000, the resulting cell would remain black.


    You can't without referring to the denominator cell itself. If that's OK,
    then base conditional formatting (Format > Conditional Formatting) on the
    sign of the denominator.

    My point was that ratios are inherrently ambiguous because +/+ is different
    than -/-, as is +/- vs -/+. There's no way to make sense out of ratios
    (percentages) without knowing the sign of the starting value.



  6. #6

    RE: How to show negative in cell?

    "Brett Romero" wrote:
    > If I divide two negative numeric cells and put the
    > result into a percentage cell, it will positive, even
    > if the change is negative. For example:
    > A B
    > 1 -6249 -5810
    > A1/B1 = 107.56%
    > The change is moving from B to A. That means I
    > should have a negative change.


    [3rd reposting to work around Google problems.]

    Repeating my response to your Jan 8 posting ....

    What makes you think so? -5810*107.56% is -6249,
    the correct answer. If you used -107.56% arbitrarily,
    you would get +6249, which is wrong.

    > How can I show that?


    Since your choice of negative "change" (factor)
    seems arbitrary, I don't know what you would
    want in all cases -- for example, B is 2 and A is
    -4, and B is -2 and A is 4. Note that when B is
    4 and A is 2, the "change" (factor) should not
    be negative.

    Your request makes more sense to me when we
    are talking about actual change, not growth factor.
    Whenever A is less than B, we might reasonably
    want to express the percentage change as negative.
    But we must use that notion of change carefully.
    For example:

    To compute percentage change (C1):

    =IF(B1=0, A1, SIGN(A1-B1)*ABS((A1-B1)/B1))

    To apply (use) percentage change to B1 (D1),
    which should equal A1:

    =IF(B1=0, C1, B1+SIGN(C1)*ABS(C1*B1))

    Examples (B1=before, A1=after):

    A1 B1 C1 D1 (should = A1)
    2 4 -50% 2
    4 2 100% 4
    -2 -4 50% -2
    -4 -2 -100% -4
    2 -4 150% 2
    -4 2 -300% -4

    Some people will quibble with my choice when B1=0.
    It is arbitrary.


  7. #7
    brett
    Guest

    Re: How to show negative in cell?

    That means if the following are used:

    A B
    1 6249 5810

    They will come out negative rather than the positive they should be.


  8. #8

    Re: How to show negative in cell?


    Brett Romero wrote:
    > If I divide two negative numeric cells and put the result into a percentage
    > cell, it will positive, even if the change is negative. For example:
    >
    > A B
    > 1 -6249 -5810
    >
    > A1/B1 = 107.56%
    >
    > The change is moving from B to A. That means I should have a negative
    > change. How can I show that?
    >
    > Thanks,
    > Brett


    Due to a Google error, reposting my response to your
    Jan 8 posting ....

    What makes you think so? -5810*107.56% is -6249,
    the correct answer. If you used -107.56% arbitrarily,
    you would get +6249, which is wrong.

    > How can I show that?


    Since your choice of negative "change" (factor)
    seems arbitrary, I don't know what you would
    want in all cases -- for example, B is 2 and A is
    -4, and B is -2 and A is 4. Note that when B is
    4 and A is 2, the "change" (factor) should not
    be negative.

    Your request makes more sense to me when we
    are talking about actual change, not growth factor.
    Whenever A is less than B, we might reasonably
    want to express the percentage change as negative.
    But we must use that notion of change carefully.
    For example:

    To compute percentage change (C1):

    =IF(B1=0, A1, SIGN(A1-B1)*ABS((A1-B1)/B1))

    To apply (use) percentage change to B1 (D1),
    which should equal A1:

    =IF(B1=0, C1, B1+SIGN(C1)*ABS(C1*B1))

    Examples (B1=before, A1=after):

    A1 B1 C1 D1 (should = A1)
    2 4 -50% 2
    4 2 100% 4
    -2 -4 50% -2
    -4 -2 -100% -4
    2 -4 150% 2
    -4 2 -300% -4

    Some people will quibble with my choice when B1=0.
    It is arbitrary.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1