I’m fairly sure this is pilot error as I’ve triend in 2 different versions of LO (updated to 5.0.3.2) and cannot get this to work. The result is always zero - 0
Calculation - =SUM(Q2:Q15) - it’s just supposed to total the numbers right?
Tools/cellcontents/autocalculate is ON
Tried format/cells/number, currency whatever - it doesn’t matter.
I’ve searched on here and found some threads that say - “go to tools/options…” - but LO 5 for OSX has no such menu item.
Any hints? Heres a pic of it in edit, as I sad the total ends up as 0.
On Mac the ‘Options’ is named ‘Preferences’. But that should not be at the core of your problem Follow Pierre-Yves. You may look in advance if some or all of your data range in column Q is bearing the cell fake numeric format (> ‘Format Cells…’ > ‘Numbers’) ‘Text’ (code @). If so change it to ‘General’ or ‘Strandard’ or whatever you find there to that effect. You may also enter the code “#.00” (without the quotation marks, of course) into the control labelled ‘Format code:’
On the screenshot numbers are aligned left which might suggest that they are considered text. In this case it is normal that the function gives zero. Either the cells were formatted as text or numbers are preceded by a quote as can be seen in the Formula Bar on this screenshot:
I kept trying different the suggestions but ultimately opened the original CSV file in OSX Numbers - and ran into the same issue. In Numbers, it was easy to change the format of the cell contents from text, to currency (or numbers) both worked equally as well and solved the issue.
But back to LO - it’s just a matter of correcting the formatting for the cells here too. But it didn’t work.
After right justifying as suggested -
I even tried changing the entire column to Format/Numbers (or Currency - didn’t work either)
It has EVERYTHING to do with this particular document. I open a new spreadsheet in LO and entered numbers, then add the SUM formula, and have no issues.
A bit frustrating, but my work around for now is to just use Numbers on this doc.
I certainly looks as though the ‘numbers’ are actually ‘text’ which will all carry the numeric value 0.
Try summing just two of the numbers to see if you get the same result.
The other option is that ONE of the numbers (if they are numbers) contains a text character (eg. 102 being entered as 1O2 - they look that same except the second one have the capital letter ‘O’ in it. This might be confusing LO as you are trying to add together fields that are not all numeric.
it might have been the case - as i’m missing the screenshot or sample file from the OP i cannot say for sure - that he was trapped by a a ‘feature’ of LO calc:
up to the actual version (6.3.0.0.alpha0) it is intentionally not! sufficient to change the format (type) of a cell, you need to ‘reenter’ or edit the content in any way, only that starts evaluating it acc. to the changed format!
(a ‘non-changing-edit’ as adding a space and deleting it while still in edit mode is sufficient for that).
col. B is! ‘right aligned’, that’s not sufficient to interpret it as a number,
cell B1 is! formatted as ‘number’, but after! input of the ‘2’, on entering the value it had been formatted as text!,
neither recalculate (F9) nor forced or ‘hard’ recalculation (strg-shift-F9) will interpret B1 as a number,
(F9 has a special ‘retarded’ behaviour while autocalculate is on, rtfm)
C1 shows a result irritating for normal users - look at the formula bar - and more irritatingly not changing acc. to the steps / action a normal user will do to get a result matching what he learned in school (as correcting the format again, triggering recalculation and so on),
imho this is a ‘bug in design’, but it might be neccessary for other - more complex - things to work, i’d discuss that with @Mike Kaganski in this thread:
he’s saying it has! to bee that way, he’s dealing with these things much longer than i do,
this answer is intended to spread the knowledge about this special behaviour, sorry for undigging an old thread for that purpose …
The “feature”? or “bug”? or whatever was found in different spreadsheet software, and in specific in Excel when I still had to use it occasionally decades ago.
The central problem (imo) is that there is no clear specification distinguishing related settings concerning the “recognition” of probably numeric input from its presentation. Details of the behaviour changed over time.
With respect to the present state I would judge it to be a bug that the “marking left apostrophe” still appears if you try to edit a number-as-text contained in a cell for which the fake format code @ was changed to a factually numeric format code. @ itself isn’t actually a format code at all, bu a recognition directive, imo.
The problem also afflicts in a different way the working of formulae for decades now,.
mho: a spreadsheet is interacting with the user, the user is interacting with the sheet,
both should be clear and unambiguously, but …
once compatibility issues with old versions and competitors products step in it’s becoming very very difficult … each decision will bee too hot, too cold, or … both …
