Sum Function Doesn't Work in Calc?

canuck_newbie · Newbie Joined: 03 Dec 2005 Posts: 4

I have pasted data into Calc from a website.

All the numbers appeared to paste properly into several columns. They are all displayed properly.

However, I cannot perform any function on the numbers.

For example, I might have

10.23
45.21

A Sum of the two cells above gives me "0" with the paste values. However, if I manually type in 10.23 and 45.21 the Sum function works.

Any work around?

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

Two thoughts. First, (sounds unlikely), check that Tools > Cell Contents > Autocalculate is selected.

Second, check the formatting of the cells and whether the contents are preceded by an apostrophe. The pasted material may be formatted as text. The formula =VALUE() will convert them to values. There's a macro on this thread which will do the same.

David

canuck_newbie · Newbie Joined: 03 Dec 2005 Posts: 4

Thank you for the suggestions.

Tools --> Cell Contents --> Autocalculate was selected.

No they were not imported into Calc as text ('10.23) values.

Not sure what the issue is. I'm halfway through re-typing the values that I need to perform calculations on, so for the moment that might be my work-around.

Dale · Posted: Mon Feb 20, 2006 8:29 pm Post subject:

For future reference:

I just tripped over this issue myself. In my particular case the problem was a space at the end of each digit. LEFT(A1;LEN(A1)-1) turned it into a number!
(VALUE did not like the trailing space)
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos

RickRandom · Super User Joined: 27 Jan 2006 Posts: 1082 Location: UK

I've had similar problems, now that Dale reminds me, but with several spaces at the end of the number. It was on data extracted from the computerised manufacturing system at work.

The quick way to look for such an occurence was to press F2, and look at whether the cursor was at the last digit, or a space or two away from the last digit.

I have used Find and Replace, i.e. find space, replace with nothing, having first selected the area with the troublesome "numbers", also selecting More Options, Current Selection Only. Otherwise there's the risk of removing all the spaces from the rst of the spreadsheet, where you do want them!

RealGrouchy · Posted: Mon Feb 20, 2006 10:22 pm Post subject:

Just to confirm, you are putting an equals sign before the sum function? (as in "=SUM(A1:B1)")?
_________________
Quite simply, OOo Impress, does not.

XPsp2, OOo 2.3, SeaMonkey 1.1.7, IE v.6.6.6...

jasondodd · Posted: Tue Feb 21, 2006 5:57 am Post subject:

I've had some website "tables" cause the same problem. Usually I can fix it by going over to the next column(say cell B1) and enter =A1*1 and copy down. That converts it to a value for sure.

good luck!

PaulDriver · General User Joined: 23 Feb 2006 Posts: 5