Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
"Stephen Connell" wrote in
message ... 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. -- David Biddulph |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 "Stephen Connell" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ck in the sStephen Connell" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 ! --------------------------------------------------------------------------- "Ron Coderre" wrote in message ... 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 "Stephen Connell" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... 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 ! -------------------------------------------------------------------------- - "Ron Coderre" wrote in message ... 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 "Stephen Connell" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 "Ragdyer" wrote: 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 ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... 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 ! -------------------------------------------------------------------------- - "Ron Coderre" wrote in message ... 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 "Stephen Connell" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 ! --------------------------------------------------------------------------- "Ron Coderre" wrote in message ... 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 "Ragdyer" wrote: 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 ! -------------------------------------------------------------------------- - "Ragdyer" wrote in message ... 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 ! -------------------------------------------------------------------------- - "Ron Coderre" wrote in message ... 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 "Stephen Connell" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 specialadd, 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 "Ragdyer" wrote in message ... 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 ! --------------------------------------------------------------------------- "Ron Coderre" wrote in message ... 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 "Ragdyer" wrote: 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 ! -------------------------------------------------------------------------- - "Ragdyer" wrote in message ... 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 ! -------------------------------------------------------------------------- - "Ron Coderre" wrote in message ... 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 "Stephen Connell" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 ! --------------------------------------------------------------------------- "Biff" wrote in message ... 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 specialadd, 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 "Ragdyer" wrote in message ... 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 ! -------------------------------------------------------------------------- - "Ron Coderre" wrote in message ... 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 "Ragdyer" wrote: 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 ! ------------------------------------------------------------------------- - - "Ragdyer" wrote in message ... 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 ! ------------------------------------------------------------------------- - - "Ron Coderre" wrote in message ... 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 "Stephen Connell" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |