Apache OpenOffice Community Forum

To total a column, I input the function =SUM(K3:K266) in cell K267, expecting a return of the total dollar value in the column. I get $0.00 as a result.
Column is set to currency and right justified.

What am I doing wrong?

Jim

Usually this happens because the values are actually text and not numbers. SUM ignores text values, even if they look like a number.

You can use View > Value Highlighting: ON to check. Numbers will display in blue, text in black.

Displayed values are in black, therefore text. How do I get the values to display as numbers?

I have highlighted the column selected cell format, selected currency, and right justified the column. All entries show as $2.34.

Changing the cell format never changes the cell content--the value stored in the cell. The format only determines how Calc will display the value. If the value stored in the cell is text, changing the format has no effect at all. (Although there are exceptional situations.)

Another good habit to adopt is to stop looking at the main sheet display. What you see there is often misleading. Check the formula bar to get some idea of what's actually in the cell.

Click on any cell that should have a number value. Look in the formula bar (the toolbar just above the cell grid). How does the value appear there?

Edit: PS:

If you see a leading apostrophe before the value shown in the formula bar, then you can change the text to numbers by this:
1) Select the cells you want to change
2) Edit > Find & Replace
Search for: .*
Replace with: &
Options/Current selection only: ON
Options/Regular expressions: ON

Click Replace All

Easiest way to get the values is to use an additional column with the formula =value(K3) entered, then copy & pasted to match the same range as used in column K

You could avoid using an extra column and use the formula =SUMPRODUCT(VALUE(K3:K266)).

I would consider this a workaround though, it would be better to find a way to get your figures into a valid number format.

and that works fine. Thank you.

This option worked for me also ("f you see a leading apostrophe before the value shown in the formula bar, then you can change the text to numbers by this:
1) Select the cells you want to change
2) Edit > Find & Replace
Search for: .*
Replace with: & (the ampersand)"

Although it worked, I would like to understand why It worked i.e. why searching for the cells with a preceding ' indicates a text cell and why replacing it with the ampersand indicates na number cell. There must be other similar protacalls that are used and not obvious. Is there a list somewhere?

The apostrophe is not part of the text value. It marks a numeric text so it won't be interpreted as a number. =LEFT(A1;1) returns the lefmost character in A1 but not any leading apostrophe in the formula bar. If you want to enter a text with a leading apostrophe you need to type 2 apostrophes.
You can format the same number 123 in thousands of different ways without changing the cell value one-hundred-twenty-three whereas the 3 character text "123" differs from the 4 character text "0123". ="123"="0123" returns FALSE
In numeric context 0123 is the exact same value as 123. Compare any formatted A1 with 123 and =A1=123 returns TRUE because 123 in A1 is always 123 no matter which number format you apply to A1.

The replacement is a regular expression. It matches any amount of characters and replaces the matched content with itself. This does the exact same thing as manually re-typing all cell contents without the apostrophe (which is not part of the text anyway).

The root of the problem is that many people are not aware of the most simple programming facts (data types text vs. number) and that they import all kinds of data from all kinds of sources ignoring most of the import options. So they often import wrong data (which has nothing to do with "formatting") and need to do strange things in order to change wrong data into right data ("formatting" does not modify any data).

Thanks. I get what you are saying regarding the ' indicating a cell with a text content . But what I don't understand yet is why searching for .* and replacing with & (ampersand) changes the cell contents to numbers.

Once I do this, I do not see the & in the formula bar(when I double click) like I saw the ' before I did the find and replace.

That is, what is the significance of the .* and the &

By the way, the spreadsheet I opened and had this problem with, was a financial spreadsheet that I was told was designed to work with Numbers and Excel. I do not use those, but had my son open the spreadsheet on his computer with both Excel and Numbers and it worked fine, without any conversions, so I assume the originator put in numbers format, not text format originally.

Thanks

Quoting part of Villeroy's post from Wed Feb 18 at 8:09 am

The replacement is a regular expression. It matches any amount of characters and replaces the matched content with itself. This does the exact same thing as manually re-typing all cell contents without the apostrophe (which is not part of the text anyway).

There are a number of posts on the forum and on the internet regarding Regular Expressions.