Autosum returns 0 value on data sheet

[EricB]

Autosum retorn a '0' Value on a Data Sheet. I have formatted the cells to
numbers and various other which has no impact. Copy & Special Paste did not
have any effect. Please assist.

 

[Corey]

did you check the range it is adding?

 

[Gord Dibben]

Eric

Format all to General or Number then copy an empty cell.

Select your data range and Paste Special(in place)>Add>OK>Esc.

Or run the range through the Data>Text to Columns Wizard making sure at third
step the Column Data Format is set to General(default).


Gord Dibben MS Excel MVP

 

[Peo Sjoblom]

Where did you get the numbers from, web page or import?

Try this, assuming the values are in A1:A20,
select A1:A20, do edit>replace, click in the find what box and hold down alt
while typing
0160, release the alt key

now sum them


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[EricB]

Hi Peo

Data Import, still no luck.

Regards

Eric

 

[Dave Peterson]

(I'm still betting that it's those HTML non-breaking spaces...)

If you select a cell in that range and retype its value, what happens to the
=sub() formula?

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[EricB]

Hi Dave

I was hoping for a quick and easy fix here, this is getting too complicated
for me. I am not technically strong enough to read and understand code and
macros.

The error seems to entail invalid spaces and characters returning a ‘0.00’
formula result. I have tried running the ‘TRIM’ & ‘CLEAN’ Formulas from the
Help menu with no luck.

Regards

Eric

 

[Roger Govier]

Hi Eric

Assuming the data being summed is in column A, then in any spare column
enter
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ","")
and copy down as required.

Now, copy this new range of cells and Paste Special>Values back over the
data in column A.
You can then delete the helper column used.

Does that resolve the problem?

 

[Roger Govier]

Hi Eric

What comma separators?

If you are saying that there is a single quote in front of the number
'2.00 then use
=--SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ","")

If you are saying there are resulting numbers like 2.00, then use
=--SUBSITITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ",""),"'","")

--
Regards

Roger Govier

Hi Roger

The Formula seems to have deleted the 'invalid' spaces, but the comma
separators (that might be a problem) is still evident. Formatting does
not
have any effect on the numbers (I can try to change the value to a
currency
or percentage without any effect), thus I am not sure if any change
has taken
place.

Regards

Eric

 

[Roger Govier]

Hi Eric

My apologies for the " ' " instead of " , " but you figured it out
anyway.
Glad you got the issue resolved.