Bug 54975 - RFE: Value Highlighting: do not highlight constant expressions as formulas
Summary: RFE: Value Highlighting: do not highlight constant expressions as formulas
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: low enhancement
Assignee: Not Assigned
Depends on:
Blocks: Value-Highlighting
  Show dependency treegraph
Reported: 2012-09-15 21:22 UTC by Dag Wieers
Modified: 2017-09-18 08:29 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:

Demo to show how value highlighting could be improved (15.33 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-10-23 16:26 UTC, Dag Wieers
Various colors (11.82 KB, image/png)
2017-09-18 08:29 UTC, Heiko Tietze

Note You need to log in before you can comment on or make changes to this bug.
Description Dag Wieers 2012-09-15 21:22:08 UTC
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:


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.
Comment 1 Joel Madero 2012-10-23 15:27:44 UTC
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 :)
Comment 2 Joel Madero 2012-10-23 15:40:29 UTC
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
Comment 3 Dag Wieers 2012-10-23 16:26:46 UTC
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 ;-)
Comment 4 Markus Mohrhard 2012-11-22 06:35:33 UTC
Not an easy hack. 

First needs UX team input because personally I would not like to mark any formula cells as fixed value.
Comment 5 Andrew Pitonyak 2012-11-25 06:41:59 UTC
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.
Comment 6 Eike Rathke 2012-11-29 15:07:17 UTC
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.
Comment 7 Joel Madero 2012-11-29 15:13:56 UTC
Eike: Should we go ahead and close this as WONTFIX?
Comment 8 Dag Wieers 2012-11-29 22:30:08 UTC
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).
Comment 9 Eike Rathke 2012-11-30 11:48:37 UTC
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.
Comment 10 Dag Wieers 2012-11-30 14:12:47 UTC
Using different colors would work indeed.

I understand that it is not a priority, and it may not be an easy undertaking.
Comment 11 Yousuf Philips (jay) (retired) 2017-09-17 20:59:20 UTC
(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?
Comment 12 Heiko Tietze 2017-09-18 08:29:16 UTC
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.