Sum - show negative number?

If I highlight several numbers in a column, Excel will indicate the
sum. But it always shows that sum as a positive number, even if it's
actually negative. Is there any way to change this behavior?

Hi,

Are you talking about the sum in the status bar. It does display the sum
as negative number as well.

Govind

Steve wrote:
> If I highlight several numbers in a column, Excel will indicate the
> sum. But it always shows that sum as a positive number, even if it's
> actually negative. Is there any way to change this behavior?

Govind <adgraj1@indiatimes.com> wrote:
>> If I highlight several numbers in a column, Excel will indicate the
>> sum. But it always shows that sum as a positive number, even if it's
>> actually negative. Is there any way to change this behavior?

>Are you talking about the sum in the status bar. It does display the sum
>as negative number as well.

Yep, the status bar. As an example, entered -600 in one cell and +500
in the next. The status bar says Sum=100.

Just played around with this a bit - apparently Excel does not
recognize numbers in red as negative, unless they're also in
parentheses - in spite of the fact that cell formatting provides the
option of unadorned red numbers to indicate negatives. This must be
some sorta bug?

Excel does not per se recognise any format as negative numbers, it
recognises negative numbers by the fact that they are less than 0, either
entered as less than zero, or as a result of a calculation. Red, and/or
parentheses are just a way of showing a number in the visual presentation of
that cell. You could just as easily format positive numbers with parentheses
as you could negative numbers.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" <msv@ods.inv> wrote in message
news:qeb0e11jvttsavaf73q66a4h45u5o41l0f@4ax.com...
> Govind <adgraj1@indiatimes.com> wrote:
> >> If I highlight several numbers in a column, Excel will indicate the
> >> sum. But it always shows that sum as a positive number, even if it's
> >> actually negative. Is there any way to change this behavior?
>
> >Are you talking about the sum in the status bar. It does display the sum
> >as negative number as well.
>
> Yep, the status bar. As an example, entered -600 in one cell and +500
> in the next. The status bar says Sum=100.
>
> Just played around with this a bit - apparently Excel does not
> recognize numbers in red as negative, unless they're also in
> parentheses - in spite of the fact that cell formatting provides the
> option of unadorned red numbers to indicate negatives. This must be
> some sorta bug?
>

Not really, if you have your values showing as red with no negative sign
that is a conscious choice you have made when selecting the formats for
those cells and Excel respects it. Why would you then expect Excel to put a
negative sign in when using the status bar. If you make your format such
that it has a negative sign as well as being red then Excel will provide a
negative sign in the status bar. Your problem stems from the fact that the
status bar does not have the facility to show a red font.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Steve" <msv@ods.inv> wrote in message
news:qeb0e11jvttsavaf73q66a4h45u5o41l0f@4ax.com...
> Govind <adgraj1@indiatimes.com> wrote:
>>> If I highlight several numbers in a column, Excel will indicate the
>>> sum. But it always shows that sum as a positive number, even if it's
>>> actually negative. Is there any way to change this behavior?
>
>>Are you talking about the sum in the status bar. It does display the sum
>>as negative number as well.
>
> Yep, the status bar. As an example, entered -600 in one cell and +500
> in the next. The status bar says Sum=100.
>
> Just played around with this a bit - apparently Excel does not
> recognize numbers in red as negative, unless they're also in
> parentheses - in spite of the fact that cell formatting provides the
> option of unadorned red numbers to indicate negatives. This must be
> some sorta bug?
>

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote:
>> >> If I highlight several numbers in a column, Excel will indicate the
>> >> sum. But it always shows that sum as a positive number, even if it's
>> >> actually negative. Is there any way to change this behavior?
>>
>> >Are you talking about the sum in the status bar. It does display the sum
>> >as negative number as well.
>>
>> Yep, the status bar. As an example, entered -600 in one cell and +500
>> in the next. The status bar says Sum=100.

>> Just played around with this a bit - apparently Excel does not
>> recognize numbers in red as negative, unless they're also in
>> parentheses - in spite of the fact that cell formatting provides the
>> option of unadorned red numbers to indicate negatives. This must be
>> some sorta bug?

>Excel does not per se recognise any format as negative numbers, it
>recognises negative numbers by the fact that they are less than 0, either
>entered as less than zero, or as a result of a calculation. Red, and/or
>parentheses are just a way of showing a number in the visual presentation of
>that cell.

That makes perfect sense - so it must mean that Excel is not capable
of showing the color red in the status line? And therefore, without
formatting the cell to include the minus sign or parentheses, there's
no way to tell whether the Sum is positive or negative?

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:
>Not really, if you have your values showing as red with no negative sign
>that is a conscious choice you have made when selecting the formats for
>those cells and Excel respects it. Why would you then expect Excel to put a
>negative sign in when using the status bar. If you make your format such
>that it has a negative sign as well as being red then Excel will provide a
>negative sign in the status bar. Your problem stems from the fact that the
>status bar does not have the facility to show a red font.

Ok, thanks, that explains it (posted my last message before I saw this
explanation). Of course, it would be nice if there was some kinda
warning or explanation when choosing that cell format option. And
even better if the status bar was capable of showing the color red.

Steve <msv@ods.inv> wrote:
>"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:
>>Not really, if you have your values showing as red with no negative sign
>>that is a conscious choice you have made when selecting the formats for
>>those cells and Excel respects it. Why would you then expect Excel to put a
>>negative sign in when using the status bar. If you make your format such
>>that it has a negative sign as well as being red then Excel will provide a
>>negative sign in the status bar. Your problem stems from the fact that the
>>status bar does not have the facility to show a red font.
>
>Ok, thanks, that explains it (posted my last message before I saw this
>explanation). Of course, it would be nice if there was some kinda
>warning or explanation when choosing that cell format option. And
>even better if the status bar was capable of showing the color red.

One reason I like to use plain red is that it makes the numbers easier
to read, without the parentheses. A second-best option would be red
with a negative sign. But that doesn't appear to be possible...

You can create a custom format to show almost *anything* you want.

Custom format for red and minus sign:

#,##0.00;[Red]-#,##0.00

And of course, you could add the parenthesis also:

#,##0.00_);[Red](-#,##0.00)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Steve" <msv@ods.inv> wrote in message
news:7kg0e1ti4vi5hca2t1u0cc8d96a7pegvmm@4ax.com...
> Steve <msv@ods.inv> wrote:
> >"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:
> >>Not really, if you have your values showing as red with no negative sign
> >>that is a conscious choice you have made when selecting the formats for
> >>those cells and Excel respects it. Why would you then expect Excel to
put a
> >>negative sign in when using the status bar. If you make your format
such
> >>that it has a negative sign as well as being red then Excel will provide
a
> >>negative sign in the status bar. Your problem stems from the fact that
the
> >>status bar does not have the facility to show a red font.
> >
> >Ok, thanks, that explains it (posted my last message before I saw this
> >explanation). Of course, it would be nice if there was some kinda
> >warning or explanation when choosing that cell format option. And
> >even better if the status bar was capable of showing the color red.
>
> One reason I like to use plain red is that it makes the numbers easier
> to read, without the parentheses. A second-best option would be red
> with a negative sign. But that doesn't appear to be possible...
>

"RagDyer" <ragdyer@cutoutmsn.com> wrote:
>> >Ok, thanks, that explains it (posted my last message before I saw this
>> >explanation). Of course, it would be nice if there was some kinda
>> >warning or explanation when choosing that cell format option. And
>> >even better if the status bar was capable of showing the color red.
>>
>> One reason I like to use plain red is that it makes the numbers easier
>> to read, without the parentheses. A second-best option would be red
>> with a negative sign. But that doesn't appear to be possible...

>You can create a custom format to show almost *anything* you want.
>Custom format for red and minus sign:
>#,##0.00;[Red]-#,##0.00
>And of course, you could add the parenthesis also:
>#,##0.00_);[Red](-#,##0.00)

Thanks, I'll give those a try. What I'd really like to be able to
customize is the status bar, to show the color red or use the minus
sign even though I haven't used it in the cells...

Personally I would never do that. I work in finance and can just imagine
the havoc that would cause when people print out in black and white, as
tables would appear not to add up etc because you wouldn't see the red.

Regards
Ken...................


"Steve" <msv@ods.inv> wrote in message
news:7kg0e1ti4vi5hca2t1u0cc8d96a7pegvmm@4ax.com...
> Steve <msv@ods.inv> wrote:
>>"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:
>>>Not really, if you have your values showing as red with no negative sign
>>>that is a conscious choice you have made when selecting the formats for
>>>those cells and Excel respects it. Why would you then expect Excel to
>>>put a
>>>negative sign in when using the status bar. If you make your format such
>>>that it has a negative sign as well as being red then Excel will provide
>>>a
>>>negative sign in the status bar. Your problem stems from the fact that
>>>the
>>>status bar does not have the facility to show a red font.
>>
>>Ok, thanks, that explains it (posted my last message before I saw this
>>explanation). Of course, it would be nice if there was some kinda
>>warning or explanation when choosing that cell format option. And
>>even better if the status bar was capable of showing the color red.
>
> One reason I like to use plain red is that it makes the numbers easier
> to read, without the parentheses. A second-best option would be red
> with a negative sign. But that doesn't appear to be possible...
>

If I selected my cells, then ctrl-clicked on an empty cell (general format),
then the statusbar showed the sum in general format.

Maybe you could try it as a workaround.

Steve wrote:
>
> If I highlight several numbers in a column, Excel will indicate the
> sum. But it always shows that sum as a positive number, even if it's
> actually negative. Is there any way to change this behavior?

--

Dave Peterson

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:
>> One reason I like to use plain red is that it makes the numbers easier
>> to read, without the parentheses. A second-best option would be red
>> with a negative sign. But that doesn't appear to be possible...


>Personally I would never do that. I work in finance and can just imagine
>the havoc that would cause when people print out in black and white, as
>tables would appear not to add up etc because you wouldn't see the red.

Good point. But I have no need to print the spreadsheets, and it
makes for easier screen viewing.

Dave Peterson <petersod@verizonXSPAM.net> wrote:
>> If I highlight several numbers in a column, Excel will indicate the
>> sum. But it always shows that sum as a positive number, even if it's
>> actually negative. Is there any way to change this behavior?

>If I selected my cells, then ctrl-clicked on an empty cell (general format),
>then the statusbar showed the sum in general format.
>Maybe you could try it as a workaround.

Sounds good, but I just tried it, and there was no change in the
status bar sum. Must be doing something wrong...

I really don't know where you come up with them from <g>

Bob


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42E0E980.B1A1DD1E@verizonXSPAM.net...
> If I selected my cells, then ctrl-clicked on an empty cell (general
format),
> then the statusbar showed the sum in general format.
>
> Maybe you could try it as a workaround.
>
> Steve wrote:
> >
> > If I highlight several numbers in a column, Excel will indicate the
> > sum. But it always shows that sum as a positive number, even if it's
> > actually negative. Is there any way to change this behavior?
>
> --
>
> Dave Peterson

I don't understand why you wouldn't want to more or less have the "best of
both worlds", where you can have YOUR "easy to see red", and still follow
convention, and include the minus and/or parenthesis.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Steve" <msv@ods.inv> wrote in message
news:6a32e1tdm6pi8h2rbm4sq52b8o4jqb015a@4ax.com...
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:
> >> One reason I like to use plain red is that it makes the numbers easier
> >> to read, without the parentheses. A second-best option would be red
> >> with a negative sign. But that doesn't appear to be possible...
>
>
> >Personally I would never do that. I work in finance and can just imagine
> >the havoc that would cause when people print out in black and white, as
> >tables would appear not to add up etc because you wouldn't see the red.
>
> Good point. But I have no need to print the spreadsheets, and it
> makes for easier screen viewing.
>

"RagDyer" <RagDyer@cutoutmsn.com> wrote:
>> >> One reason I like to use plain red is that it makes the numbers easier
>> >> to read, without the parentheses. A second-best option would be red
>> >> with a negative sign. But that doesn't appear to be possible...
>>
>> >Personally I would never do that. I work in finance and can just imagine
>> >the havoc that would cause when people print out in black and white, as
>> >tables would appear not to add up etc because you wouldn't see the red.
>>
>> Good point. But I have no need to print the spreadsheets, and it
>> makes for easier screen viewing.

>I don't understand why you wouldn't want to more or less have the "best of
>both worlds", where you can have YOUR "easy to see red", and still follow
>convention, and include the minus and/or parenthesis.

Well, the numbers are certainly easier to read without the
parentheses. I guess the minus sign would be ok though...

I just tried it again (I use xl2003) and when I hit that ctrl-click on the empty
cell, the number on the statusbar changed from 2 to -2 (it was negative all the
time).



Steve wrote:
>
> Dave Peterson <petersod@verizonXSPAM.net> wrote:
> >> If I highlight several numbers in a column, Excel will indicate the
> >> sum. But it always shows that sum as a positive number, even if it's
> >> actually negative. Is there any way to change this behavior?
>
> >If I selected my cells, then ctrl-clicked on an empty cell (general format),
> >then the statusbar showed the sum in general format.
> >Maybe you could try it as a workaround.
>
> Sounds good, but I just tried it, and there was no change in the
> status bar sum. Must be doing something wrong...

--

Dave Peterson

Thanks, er, I think. <vbg>

Bob Phillips wrote:
>
> I really don't know where you come up with them from <g>
>
> Bob
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42E0E980.B1A1DD1E@verizonXSPAM.net...
> > If I selected my cells, then ctrl-clicked on an empty cell (general
> format),
> > then the statusbar showed the sum in general format.
> >
> > Maybe you could try it as a workaround.
> >
> > Steve wrote:
> > >
> > > If I highlight several numbers in a column, Excel will indicate the
> > > sum. But it always shows that sum as a positive number, even if it's
> > > actually negative. Is there any way to change this behavior?
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

It's a compliment. Off the wall thinking <bg>

Bob


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42E18D2D.F2D0C1D9@verizonXSPAM.net...
> Thanks, er, I think. <vbg>
>
> Bob Phillips wrote:
> >
> > I really don't know where you come up with them from <g>
> >
> > Bob
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:42E0E980.B1A1DD1E@verizonXSPAM.net...
> > > If I selected my cells, then ctrl-clicked on an empty cell (general
> > format),
> > > then the statusbar showed the sum in general format.
> > >
> > > Maybe you could try it as a workaround.
> > >
> > > Steve wrote:
> > > >
> > > > If I highlight several numbers in a column, Excel will indicate the
> > > > sum. But it always shows that sum as a positive number, even if
it's
> > > > actually negative. Is there any way to change this behavior?
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

I think the current (well, 1990's) terminology is "thinking out of the box".

"Off the wall thinking" is much nicer <bg>.

Bob Phillips wrote:
>
> It's a compliment. Off the wall thinking <bg>
>
> Bob
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42E18D2D.F2D0C1D9@verizonXSPAM.net...
> > Thanks, er, I think. <vbg>
> >
> > Bob Phillips wrote:
> > >
> > > I really don't know where you come up with them from <g>
> > >
> > > Bob
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:42E0E980.B1A1DD1E@verizonXSPAM.net...
> > > > If I selected my cells, then ctrl-clicked on an empty cell (general
> > > format),
> > > > then the statusbar showed the sum in general format.
> > > >
> > > > Maybe you could try it as a workaround.
> > > >
> > > > Steve wrote:
> > > > >
> > > > > If I highlight several numbers in a column, Excel will indicate the
> > > > > sum. But it always shows that sum as a positive number, even if
> it's
> > > > > actually negative. Is there any way to change this behavior?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42E19C1D.3B1109FE@verizonXSPAM.net...
> I think the current (well, 1990's) terminology is "thinking out of the
box".

I know, and because it is so ubiquitous, I avoid it like the plague.

> "Off the wall thinking" is much nicer <bg>.

I think so too <ebg<

Dave Peterson <petersod@verizonXSPAM.net> wrote:
>> >> If I highlight several numbers in a column, Excel will indicate the
>> >> sum. But it always shows that sum as a positive number, even if it's
>> >> actually negative. Is there any way to change this behavior?
>>
>> >If I selected my cells, then ctrl-clicked on an empty cell (general format),
>> >then the statusbar showed the sum in general format.
>> >Maybe you could try it as a workaround.
>>
>> Sounds good, but I just tried it, and there was no change in the
>> status bar sum. Must be doing something wrong...

>I just tried it again (I use xl2003) and when I hit that ctrl-click on the empty
>cell, the number on the statusbar changed from 2 to -2 (it was negative all the
>time).

I'm using xl2000, maybe that's why it doesn't work?

I'm using XL2000 here and it works.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" <msv@ods.inv> wrote in message
news:nvk4e1dcrn74ig6soqv28eftkqsqlaupdt@4ax.com...
> Dave Peterson <petersod@verizonXSPAM.net> wrote:
> >> >> If I highlight several numbers in a column, Excel will indicate the
> >> >> sum. But it always shows that sum as a positive number, even if
it's
> >> >> actually negative. Is there any way to change this behavior?
> >>
> >> >If I selected my cells, then ctrl-clicked on an empty cell (general
format),
> >> >then the statusbar showed the sum in general format.
> >> >Maybe you could try it as a workaround.
> >>
> >> Sounds good, but I just tried it, and there was no change in the
> >> status bar sum. Must be doing something wrong...
>
> >I just tried it again (I use xl2003) and when I hit that ctrl-click on
the empty
> >cell, the number on the statusbar changed from 2 to -2 (it was negative
all the
> >time).
>
> I'm using xl2000, maybe that's why it doesn't work?
>

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote:
>> >> >> If I highlight several numbers in a column, Excel will indicate the
>> >> >> sum. But it always shows that sum as a positive number, even if
>it's
>> >> >> actually negative. Is there any way to change this behavior?
>> >>
>> >> >If I selected my cells, then ctrl-clicked on an empty cell (general
>format),
>> >> >then the statusbar showed the sum in general format.
>> >> >Maybe you could try it as a workaround.
>> >>
>> >> Sounds good, but I just tried it, and there was no change in the
>> >> status bar sum. Must be doing something wrong...
>>
>> >I just tried it again (I use xl2003) and when I hit that ctrl-click on
>the empty
>> >cell, the number on the statusbar changed from 2 to -2 (it was negative
>all the
>> >time).
>>
>> I'm using xl2000, maybe that's why it doesn't work?

>I'm using XL2000 here and it works.

hmmm, wonder what the heck I'm doing wrong...

I formatted my cells by:

selecting the range
format|cells|number tab
Number (0 decimals) and red negative numbers.

And that unused cell was formatted as General.

(and I ctrl-clicked on that unused cell after selecting the other range.)

Did you do anything that varied from that?

If yes, explain what you did to see if anyone can duplicate it. (but try it one
more time using this technique.)

Steve wrote:
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote:
> >> >> >> If I highlight several numbers in a column, Excel will indicate the
> >> >> >> sum. But it always shows that sum as a positive number, even if
> >it's
> >> >> >> actually negative. Is there any way to change this behavior?
> >> >>
> >> >> >If I selected my cells, then ctrl-clicked on an empty cell (general
> >format),
> >> >> >then the statusbar showed the sum in general format.
> >> >> >Maybe you could try it as a workaround.
> >> >>
> >> >> Sounds good, but I just tried it, and there was no change in the
> >> >> status bar sum. Must be doing something wrong...
> >>
> >> >I just tried it again (I use xl2003) and when I hit that ctrl-click on
> >the empty
> >> >cell, the number on the statusbar changed from 2 to -2 (it was negative
> >all the
> >> >time).
> >>
> >> I'm using xl2000, maybe that's why it doesn't work?
>
> >I'm using XL2000 here and it works.
>
> hmmm, wonder what the heck I'm doing wrong...

--

Dave Peterson

Dave Peterson <petersod@verizonXSPAM.net> wrote:
>I formatted my cells by:
>selecting the range
>format|cells|number tab
>Number (0 decimals) and red negative numbers.
>And that unused cell was formatted as General.
>(and I ctrl-clicked on that unused cell after selecting the other range.)

Thanks Dave, this works. I think the problem was that the ctrl-click
cell was not formatted as General.

woohoo!!!

Glad you got it working.

Steve wrote:
>
> Dave Peterson <petersod@verizonXSPAM.net> wrote:
> >I formatted my cells by:
> >selecting the range
> >format|cells|number tab
> >Number (0 decimals) and red negative numbers.
> >And that unused cell was formatted as General.
> >(and I ctrl-clicked on that unused cell after selecting the other range.)
>
> Thanks Dave, this works. I think the problem was that the ctrl-click
> cell was not formatted as General.

--

Dave Peterson