Sum function

[Guest]

I have a spreadsheet in which the sum function returns a zero value from a
range with positive numbers. The sum formula is correctly entered. Any ideas?

Thanks

 

[David Biddulph]

I have a spreadsheet in which the sum function returns a zero value from a
range with positive numbers. The sum formula is correctly entered. Any
ideas?

The usual explanation is that the "numbers" aren't actually numbers, but
cells formatted as text.

 

[Guest]

It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data><text-to-columns>....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

 

[Ragdyer]

Right click a new, unused cell, and choose "Copy".
Select the "bad" numbers.
Right click in that selection and choose "Paste Special".
Click on "Add", then <OK>.

This should transform all 'text' numbered cells to *real*, XL recognized
numbers.

 

[Ragdyer]

TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

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

It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data><text-to-columns>....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

I have a spreadsheet in which the sum function returns a zero value from a
range with positive numbers. The sum formula is correctly entered. Any ideas?

Thanks

 

[Ragdyer]

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text' formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

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

TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

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

It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data><text-to-columns>....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

I have a spreadsheet in which the sum function returns a zero value

from

a

 

[Guest]

Thanks, RD....(I was wondering why TTC was working ok on *my* PC. )

But, I think your suggestion of Copy/Paste_Special.Add is the better way to
go.

***********
Best Regards,
Ron

XL2002, WinXP

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text' formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

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

TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

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

It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data><text-to-columns>....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP


:

I have a spreadsheet in which the sum function returns a zero value

from

a
range with positive numbers. The sum formula is correctly entered. Any ideas?

Thanks

 

[Ragdyer]

I prefer TTC *if* the data is in a single column.

The "Paste Special", of course, is the better way to go for global or
multi-column data revisions.
--
Regards,

RD

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

Thanks, RD....(I was wondering why TTC was working ok on *my* PC. )

But, I think your suggestion of Copy/Paste_Special.Add is the better way to
go.

***********
Best Regards,
Ron

XL2002, WinXP

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text' formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

--------------------------------------------------------------------------

-

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

!

--------------------------------------------------------------------------

-

TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

--------------------------------------------------------------------------

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

benefit !
--------------------------------------------------------------------------

-
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data><text-to-columns>....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP


:

I have a spreadsheet in which the sum function returns a zero

value
from

a
range with positive numbers. The sum formula is correctly entered. Any
ideas?

Thanks

 

[Biff]

To add some more confusion <g>

The copy an empty cell/paste special method:

If the cell being copied is formatted as TEXT and after the paste
special>add, the SUM will return the correct result but the value returned
still "appears" to be TEXT (cell is aligned left). However, is you test
that value: =ISNUMBER( ) returns TRUE, =ISTEXT( ) returns FALSE. If you
check the format of the SUM cell is will show as TEXT.

Isn't this stuff loads 'o fun?

Biff

I prefer TTC *if* the data is in a single column.

The "Paste Special", of course, is the better way to go for global or
multi-column data revisions.
--
Regards,

RD

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

Thanks, RD....(I was wondering why TTC was working ok on *my* PC. )

But, I think your suggestion of Copy/Paste_Special.Add is the better way to
go.

***********
Best Regards,
Ron

XL2002, WinXP

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text'
formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

--------------------------------------------------------------------------

-

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

!

--------------------------------------------------------------------------

-

TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data><text-to-columns>....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP


:

I have a spreadsheet in which the sum function returns a zero value
from
a
range with positive numbers. The sum formula is correctly
entered. Any
ideas?

Thanks

 

[Ragdyer]

Of course I'm nit-picking <bg>, BUT ...
I, and "most" of what I've read in these groups, DO stipulate:
"new, unused cell"
Which of course means that the *unused* cell is formatted to the XL default
'General' format.

This does mean however, that the OP will *always* know which cells are/have
been, unused.
That is, of course, it they even noticed the 'unused' stipulation in the
instructions in the first place.<g>
--
Regards,

RD

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

To add some more confusion <g>

The copy an empty cell/paste special method:

If the cell being copied is formatted as TEXT and after the paste
special>add, the SUM will return the correct result but the value returned
still "appears" to be TEXT (cell is aligned left). However, is you test
that value: =ISNUMBER( ) returns TRUE, =ISTEXT( ) returns FALSE. If you
check the format of the SUM cell is will show as TEXT.

Isn't this stuff loads 'o fun?

Biff

I prefer TTC *if* the data is in a single column.

The "Paste Special", of course, is the better way to go for global or
multi-column data revisions.
--
Regards,

RD

--------------------------------------------------------------------------

-

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

!

--------------------------------------------------------------------------

-

way
to

------------------------------------------------------------------------- -
- benefit
!

------------------------------------------------------------------------- -
- numbers,
TTC

------------------------------------------------------------------------- -

------------------------------------------------------------------------- -

-
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data><text-to-columns>....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP


:

I have a spreadsheet in which the sum function returns a zero value
from
a
range with positive numbers. The sum formula is correctly
entered. Any
ideas?

Thanks

 

[Pete_UK]

Another way is to enter this array* formula instead of your normal sum(
) formula:

=SUM(VALUE(range of cells))

*As this is an array formula, once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of the
usual ENTER. If you do this correctly then Excel will add curly braces
{ } around the formula - you must not type these yourself.

Though changing the text to numbers as described above is the better
way if you want to use the numbers in other calculations, this gives
you an alternative.

Hope this helps.

Pete