Excel not adding values

[Suggie]

Does anyone know why excel does not add values in a cell sometimes?

For example, I have a row with the below values

0.2
0.5
10
12.6

And when I try to sum it returns a vaule of zero?

Any ideas??

TIA,

 

[gizmo1973]

Is your format set to accomodate the 0.3
Setting your decimals on!

Regards, Phil.
C.E.O. Bodgeit, Leggit & Scarper. International.
"Stuffing things up completely since 1973"

 

[xlbo]

are the values actually text ??
use
=ISTEXT(cell_ref)
to determine this

If the result is TRUE then you will need to convert to true numbers

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Suggie]

Yes, it came back as yes, it may be because there are spaces, how do I convert this type of cell to numbers?

 

[xlbo]

Use the "Times 1 Fix"

Enter a 1 into any blank cell
copy it
select the "numbers" you want to convert
Edit>PasteSpecial
Tick Values and MULTIPLY

et voila

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Suggie]

Geoff,

Tried that, but it still has the spaces in the cell??

I've tried the substitute formula but it's not working....

TIA

 

[xlbo]

Do a Find And Replace "" for " " 1st - then run the "fix"

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Suggie]

Tried that, it says it cannot find the value??

I have tried substitute and the below macro and it doesn't work. The orginal cell has 4 blank spaces after the value and I can't remove them all?

TIA,
Mark

Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr(strInput, " "))
GoTo Test
End If
End Function

 

[xlbo]

Hmmm - maybe they ain't spaces then....

If they all have 4 "spaces" after the end, you could use a formula like

=value(left(A1,len(A1)-4))

and then copy/paste special as values

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Eugene99]

Try selecting a cell and hitting F2. It should place the cursor on the right side of the last character. Then hit Home. It should place the cursor on the left side of the first character. This will tell you if you have any spaces.

Also, when you hit Find, make sure that you do not have a check mark in the field that says Match Case or Find Entire Cells Only.

Hope this helps...
Eugene

 

[macropod]

Hi,

It could be that some of the spaces were created via Chr160 instead of the usual Chr32. The macro I posted in:

http://www.tek-tips.com/viewthread.cfm?qid=915838

will clean those out too. You could leave out the stuff for providing feedback, etc if you don't need it.

Cheers