 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2006Sum 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 >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.  SignatureDavid Biddulph 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 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. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > It sounds like the "numbers" are really numeric text. > [quoted text clipped - 14 lines] > > > > Thanks 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! SignatureRegards, 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. [quoted text clipped - 20 lines] > > > > > > Thanks 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 [quoted text clipped - 30 lines] > > > > > > > > Thanks 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. SignatureRegards, 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. ) > [quoted text clipped - 49 lines] > > > > > > > > > > Thanks 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. > [quoted text clipped - 64 lines] >> > > > > >> > > > > Thanks 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> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > To add some more confusion <g> > [quoted text clipped - 83 lines] > >> > > > > > >> > > > > Thanks 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. SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > 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 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.