Problem adding up

[srs]

I have number data in cell C2 to C19 which i want to add up. when i enter
the formula =SUM(C2:C19) i get the answer zero. I cant make out why. I
wonder any virus or corruption is the cause or my i making any mistake. I
apreciate any suggestions. Thanks. sr

 

[Frank Kabel]

Hi
your values in C2:C19 are probably stored as 'Text'. What does the
formula
=ISNUMBER(C2)
return. It should return TRUE

 

[srs]

Thanks for reply. It is returning "False". In fact i am storing the
"amount" in the column header as "currancy" not as text. Why is it not
storing as currancy or number . What should do? please suggest.

 

[Frank Kabel]

Hi
how have you entered these values. Looks like they are textg values
(eventually imported?).
Try the following:
- select an empty cell and copy it
- select your values
- goto 'Edit - Paste Special' and choose 'Add'

this should convert the text values to numbers

 

[Peo Sjoblom]

Since it seems Excel sees it as text, can you copy one cell and post it?
If it is £number try to do edit>replace and replace the £ with noting (leave
replace box empty)
Note that unless you use any alignment text is left aligned and numbers
right aligned so if you
see that your numbers move to the right they are numbers and will sum.. Then
select them again
and do format>cells>numbers and select currency

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[srs]

I copied and pasted special, still excell is returning value as "zero". It
is the same when open a new file and do some sums. I have reinstalled
Microsoft Office Professional, still the result is the same. I am really
lost. I apreciate any more suggestions. Thanks-sr

 

[Peo Sjoblom]

First of all, don't uninstall/reinstall anything, it has nothing to do with
that.
How are the data entered/imported into excel?

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[srs]

i am copying a cell and posting it. 679.40
all the currancy numbers are aligning automatically to "custom " din etc.
what is happening? thanks sr

 

[Peo Sjoblom]

OK, do as follows..
Open the workbook with the text numbers, press alt + F11, now click
insert>module,
in the window that opens paste in the following

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
press alt + Q to close the VBE, now select all "numbers",press alt + F8 and
run TrimallNow format as currency
--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
did Peo's suggestion using the TrimAll macro work for you. Sounds like
you have some additional characters in this cell. e.g. spaces or other
invisible characters

 

[CLR]

I think that after trying all the suggestions of Frank and Peo and if they
didn't work, I would just open a new work book and re-type the 17 entries by
hand, making sure they were entered as numbers and not text and typing
nothing in the cell except numbers. If you want currency formatting, that
can be added with Right-click > Format Cells Number Tab, and either
currency or custom..........sometimes, the Dragon wins <g>

Vaya con Dios,
Chuck, CABGx3