Weird Calculation and Sorting Problems in OpenOffice Calc Spreadsheets and Writer Tables

This is a followup of my post yesterday about correct cell format.

Sometimes weird stuff happens in cells.

• Calculations don't work. They just sit there displaying the formula instead of running and displaying the results.

• Date formats don't work. You type in a perfectly good date and some bizarre number appears instead.
• Sorting doesn't work. Sorting numbers might change the order, but to an order you don't expect.

Why? Because the cells think that they are text. So it doesn't make sense to do calculations. You can't multiple bread * milk, so  you can't multiply two other things that Openoffice thinks are text.

This happens in Calc spreadsheets and Writer tables. To find out if the contents of your cells are deluded about their identity, select them, right-click, and choose Number Format.

If you see this, then the cells have the wrong format.

To correct this in Writer, just select the correct format and click OK.

To correct this in Calc:

• Close the number formatting window
• Select the problem cell
• Cut out the contents -- be sure to select and cut the series of characters in the cell, not the cell itself
• Reselect the entire cell, then press Delete
• In the window that appears, be sure that Format is selected. Click OK.

• With the cell still selected, right-click again and choose Format Cells. Select the correct format and click OK.

• Choose Edit > Paste Special
• Choose Unformatted Text and click OK

• Now the calculation should work correctly. And if it wasn't a calculation but a number, now rerun the sort and it should work correctly.

See yesterday's post about number recognition, toward the end, for how to recognize a badly formatted cell that thinks it's text, even though it might have a $ and decimal places.

---

Comments

[Oifan]

Hello,

I had more of these weird cells, and I found out that the problem is with their Style.
When I selected all these cells, right-clicked and selected "Default" style, all formulas are acting OK (but you'll lose original formatting). Maybe it's possible to define custom formatting(s) and apply this to these cells.

[Gytis]

Had the same problem with showing formula instead of calculating the cell, though cell was in number format though.
The cause was in my old habit in writing formula - I used to put + (plus) sign to show it will be content to calculate instead of text. (example - "+2+3")
In OO 2.3 version the + does not work anymore - you have to put = sign.(example - "=2+3")

[Spread sheet]

I have a table in writer and I wish to add 1 month to say January.. can someone let me know what is the formula to be entered into the cell

[Solveig]

Hi Spread sheet,

I'm afraid I don't understand the question. Can you give more detail?

If you're doing anything complicated I would say do it in a spreadsheet, then copy and paste it, if necessary, into Writer. Copy, then choose Edit > Paste Special. Choose Link and you can edit it in the spreadsheet and it'll update in the Writer doc. Choose Formatted Text and it will be pasted into Writer as a normal table.

Do a normal paste and you can double-click the spreadsheet in the Writer doc and edit it with all the usual spreadsheet tools.

Solveig

[MSS]

If you're doing anything complicated I would say do it in a spreadsheet, then copy and paste it, if necessary, into Writer. Copy, then choose Edit > Paste Special. Choose Link and you can http://www.batterygoshop.co.uk/asus/a42-a4.htm asus a4 a4d a4g a4k a4l a4s a4000 a42-a4 ,
edit it in the spreadsheet and it'll update in the Writer doc. Choose Formatted Text and it will be pasted into Writer as a normal table.

Do a normal paste and you can double-click the spreadsheet in the Writer doc and edit it with all the usual spreadsheet tools.

[Velvet]

Hi! This is helpful indeed! But I hope you can help me. I can't seem to resolve my sorting problem. I already used until row 1170 and I need to sort by date in one column. The problem is, if I sort one date, for example April 20, I get April 20 to 28. But I just need to display April 20. In excel this works and I wonder why Calc doesn't recognize this.