Can't sum a series of numbers

[Cathy]

I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then when I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells which
I've not tried that might work? Any other suggestions to get them to total
up?

Thanks in advance for any help you can render.

 

[RagDyer]

Right click in a new, empty, unused cell, and choose "Copy".

Select all the "bad" cells that won't sum.
Right click in that selection and choose "Paste Special".
Click on "Add",
then <OK>,
then <Esc>.

And you should now have XL recognizable, summable, numbers.

 

[Max]

Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit > Paste special > Multiply > OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[RagDyer]

Max, FWIW
One point about the "multiply" option as opposed to the "add" option.

If some of the cells are not contiguous, with the "add" option, you could
select an all encompassing block of cells, with an easy single selection,
that could go beyond the cells in question, and include empty and/or blank
surrounding cells.

If you tried this with the "multiply" option, those empty cells would become
filled with 0's.

 

[Cathy]

Thanks for the repsonse. I tried both the multiply and the add special paste
and neither worked. I'm still manually keying data to get it to work but
would really appreciate anything else to try to get the sum to work.

 

[Cathy]

I tried both the paste special multiply and add and neither worked. I would
appreciate any other suggestions to get this to work.

 

[Gord Dibben]

If the multiply or add methods won't work make sure you first format the
cells as General, including the blank copied cell.


Gord Dibben MS Excel MVP

 

[RagDyer]

It really wouldn't matter Gord, if that empty cell was a Text cell.

The "Paste Special", with either add or multiply would convert those "bad"
cells to calculable numbers.

 

[RagDyer]

Another guess would be that nemesis of imported data, the "non-breaking
space", char(160).

If your values have that character as a prefix or suffix, the data would
remain incalculable.

Try this:

Select the "bad" cells, then from the Menu Bar,
<Edit> <Replace>
In the "Find What" box, enter:
<Alt> 0160
using the numbers from the Num keypad, *not* the numbers under the function
keys.

Leave the "Replace With" box empty,
And click <Replace All>.

See if that works.

It may be that you perhaps need *both* procedures to convert your values.

 

[Gord Dibben]

You rae simply correct<g>

Thanks RD


Gord

 

[Max]

Agreed, RD on the point of any empty cells within the range becoming zero,
albeit this won't affect the OP's intent to sum. The subtle point about the
multiply option is that by entering a "1" into an "empty" cell, that in
itself ensures that the "empty" cell will contain a: 1, and not an invisible
whitespace(s) <g>. I use both methods.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik