I have various spreadsheets for calculating income and costs. I discovered a very valuable feature in LO 3.6 named "Value Highlighting" however it incorrectly highlights fixed mathematical calculations as a formula, while it should have been considered as a value. Let me explain. In a cost-calculation, certain costs are montly, quarterly or yearly costs. Since you ought to have them on a (say) monthly basis, the quarterly and yearly costs need to be recalculated. So a column stating: 20 =180/12 =34/3 each displaying the monthly cost for a given item, each should be considered a value since each of them are a fixed entry. It shouldn't matter whether this fixed entry was in fact a value on a monthly basis or a refactored value (still indicating a number that is traceable e.g. through an invoice). So I would propose that LibreOffice considers formulas with no references to other cells or ranges as values for Value Highlighting. That would make more sense in all cases. And since it's 'Value Highlighting' is confusing in this case too, I would rename it to 'Input Highlighting' because that's what is really relevant. Everything is a value, but not everything is considered input.
Can you provide a test case just so we can easily see the issue? We just like working off a document that the bug submitter has personally created and seen the issue with so then we see the identical stuff. Marking as NEEDINFO. If this isn't possible please just let us know and reopen as UNCONFIRMED, otherwise please attach a simple document and mark as UNCONFIRMED. I am going to test now regardless but I'm not in a position to upload a document and it would be much better if we could have one that you created. Thanks for your understanding and help. Sounds like a reasonable enhancement request at first glance :)
You know what, nevermind, way too simple to reproduce. Marking as NEW and prioritizing Enhancement: Not really a bug, just how it was designed but it could be made better and your recommendation seems like the way to go Low: Not many people use this feature and it's a minor issue regardless. ProposedEasyHack: Honestly not sure about this but could be so I marked it, one of our devs will have to confirm this
Created attachment 68958 [details] Demo to show how value highlighting could be improved OK, I read your first comment/mail then created a test-case, and only now when posting it saw your second comment/mail. So I might as well attach it ;-)
Not an easy hack. First needs UX team input because personally I would not like to mark any formula cells as fixed value.
I just wanted to add a few thoughts in case anyone chooses to take this seriously and pursue it. I will start with the following: Calc "incorrectly highlights fixed mathematical calculations as formulas". I would prefer if you stated that you desired Calc to have some ability to figure out that certain types of formulas may be able to be treated as constants for some particular purpose. The trick, of course, is that it is a formula, and even if another cell is not directly referenced, some of the side effects may cause the value to not be static. When ever a function or operator is used, it is difficult to know that the operator will not do something that is not directly obvious, so if this is pursued, I expect that it would read more like.... A formula containing only constant values and only uses operators from some specific set and can only call / use functions from a specific set. Where can this go wrong? consider what happens if an operator or function returns a different value based on a date or time. In my world, it is not uncommon to write your own function that may directly accesses other stuff in a way that is not obvious by looking at the parameters. For example, a function that knows that it should look at a specified set of cells that control what is returned. This is legal, it just cannot modify any cell on the sheet that called the function. That said, limiting cells to only contain arithmetic operators with constant operands would likely suffice, but I expect that the change will require a change to published interfaces and services ( because you absolutely do NOT want a cell to state that it is a constant value when it is really a formula, because then every other piece that wants to treat it in a certain way because it is formula will fail ) when I need to deal with that, I usually simply do a copy and then "paste special" and I paste in the values of interest.
From a user's point of view I would find it confusing to have some formulas highlighted as numeric values and some highlighted as formulas, not knowing the exact reasons and differences why it is so. The current behavior is simple and memorable, formulas are highlighted as formulas and not highlighted as numeric values.
Eike: Should we go ahead and close this as WONTFIX?
I concur, whether I put "150" in a cell, or instead "=1800/12". Both are a different representation of 150. There is no influence from outside, the value will be the same. There are reasons for people to use "=1800/12" or "=100+50" because it adds specific information into the cell that would otherwise be guessing. I'd make a distinction between a cell that uses functions or other cells, and cells that don't. Seems more useful, especially in the cases where people do what I do (and from experience I know this is often done in accounting/TCO calculations).
Such constant expressions could be marked in a different color, not blue nor green, maybe red or whatever to indicate it's a expression that never changes. However, we certainly have more important work to do, but if someone feels an itch feel free to scratch and patch ;-) I'm changing subject of this RFE because highlighting is not incorrect, it's just not what the reporter would like to see.
Using different colors would work indeed. I understand that it is not a priority, and it may not be an easy undertaking.
(In reply to Eike Rathke from comment #9) > Such constant expressions could be marked in a different color, not blue nor > green, maybe red or whatever to indicate it's a expression that never > changes. As red is commonly used with 'negative numbers in red', i'd suggest using cyan (blue + green) [#008080] to differentiate constant expressions from numbers (blue) and formulas (green). heiko, stuart, cor: what's your take?
Created attachment 136334 [details] Various colors Good idea to mix blue and green. Though #008080 is barely to distinguish from each other so I suggest to use violet if the difference is important or light teal 1 in case it's less relevant (my take here). Ideally, the complete set of highlighting colors can be changed.