Summary: | Formula does not calculate due to prior Format Cell as Text, despite Format Cell as Number | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | walkerkorea |
Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | RESOLVED NOTABUG | ||
Severity: | normal | CC: | ahamedckmgm, cno, dennisroczek, ilmari.lauhakangas |
Priority: | medium | ||
Version: | 5.1.1.3 release | ||
Hardware: | All | ||
OS: | All | ||
See Also: |
https://bugs.documentfoundation.org/show_bug.cgi?id=33962 https://bugs.documentfoundation.org/show_bug.cgi?id=101696 |
||
Whiteboard: | |||
Crash report or crash signature: | Regression By: | ||
Attachments: |
Formula not seen evaluated
The precedent cells are seen highlighted on double clicking |
Description
walkerkorea
2016-05-12 04:03:15 UTC
This isn't a bug. It works how it's intended to work. There is an enhancement request to bring text to number into LibreOffice core (currently it's an extension). If a user goes to the extent of formatting a cell as text - of course LibreOffice is going to treat it like text. Is that you, Roy? (The IT Crowd reference) So, you're claiming: 1. A cell becoming Text is always intentional. 2. Such a change should be irrevocable. Both of these are false. Well, you can write software where #2 is true, but I doubt many users will appreciate a spreadsheet that cannot be modified more than once. There are many ways for cells to become Text without that being intentional, even with power users of LO. Users can need something to be Text, but only temporarily, with the later intention of changing it to a date or a number. More commonly, any time you insert a row or column, LO is copying the propert(ies) of adjacent cells, and that's not always ideal. Users might have intended to have clicked on the item just before, and so accidentally convert a cell, row, column or entire sheet to Text, with no obvious change that they would quickly Undo from. So, yes, any edit should be able to be changed again, with reversible results. Otherwise, you have a cell that looks normal, like any other empty cell, but yet when you insert a number or formula, it doesn't work like one. Btw, all the many "It's not a bug, it's a feature" replies are just disheartening for users like me who take our own time to research if it's already reported, and then report the bug. Eventually, someone will take this serious enough to fix it. It's a HUGE issue for anyone who uses Text. And, yes, of course I tried turning it off and back on! Correcting status to unconfirmed. Hi Walker, Added some reference issues.. Hi all, This problem is posing a going back to MS excel in my office. After an herculian effort only I could change the mood of the staff in my office towards Libreoffice. We need a lot of calculations for the day2day office work. At times, the cell shows only the equation. It does not get updated. If we double click the precedent cells are high lighted with colour bands. It behaves as a text cell. There is no ' character before the = sign. I can repeat this like this, 0. let in the cell A3 there is an equation "=A1+A2" (of course no double quotes) gives the correct result by adding A1 and A2 1.put a ' character in the eqn. before = sign 2.convert the cell A3 to text using format cells 3.then remove the ' character ... it shows instead of the function value, the function as a text. 4.trying to convert back from text to number format does not yield the result.. it shows the eqn. rather than the value Pl. let me know what is the workaround for this. I am using Linux and 4.3.3.2 under debian. In my office MS Winodws 8.0 with LibreOffice 5.2 also have the same problem. Rasheed The behaviour is strange and i do not know how that happen in a document.pl. post a work around. Hi Ahmed, (In reply to Ahamed from comment #6) > The behaviour is strange and i do not know how that happen in a document.pl. > post a work around. How do you get a real life document where formulas are formatted like that? But, if it's important, feel free to contact me to look at possibilities to extend CT2N to serve this very goal. https://extensions.libreoffice.org/extensions/ct2n-convert-text-to-number-and-dates You may want to extend it yourself too, of course. Hope that helps, Cor I'm tempted to close as NotABug, by the way (In reply to Cor Nouws from comment #7) > Hi Ahmed, > > (In reply to Ahamed from comment #6) > > The behaviour is strange and i do not know how that happen in a document.pl. > > post a work around. > > How do you get a real life document where formulas are formatted like that? > But, if it's important, feel free to contact me to look at possibilities to > extend CT2N to serve this very goal. > > https://extensions.libreoffice.org/extensions/ct2n-convert-text-to-number- > and-dates > You may want to extend it yourself too, of course. > > Hope that helps, > Cor Hi Cor Nouws, appreciate for your immediate reply. The issue was while importing an XL document to Libreoffice and while entering an equation to find a result based on the data in some other cells, it showed only the equation rather than the result. I tried to reformat the cell using Ctl+1 back to number format but was in vain. Let me know what is the workaround for this. by the by, How to use that CT2N command Ahamed Created attachment 131230 [details]
Formula not seen evaluated
Created attachment 131231 [details]
The precedent cells are seen highlighted on double clicking
The precedent cells are seen highlighted on double clicking
the CT2N extension does not give the formula back to the value instead it gives only formula itself. (In reply to Ahamed from comment #12) > the CT2N extension does not give the formula back to the value instead it > gives only formula itself. That is what I wrote in comment #7 - sorry if that was not clear. Closing per a Calc dev's feedback: "applying display formats never changes cell content" For clarification: if a cell is already formatted to Text, any new cell input is forced to text content, no number, date or formula recognition is attempted. This is on purpose and works similar across major spreadsheet applications. As changing the cell format does not change the cell content, to force a new interpretation of content as formula first select the cell range in question, then change the cell format to General, then, with the selection still in place, use Find&Replace (Ctrl+H): Find: = Replace: = verify that under Options the Selection only is checked and hit Replace All. |