Bug 85264 - RFE: computed conditional formatting
Summary: RFE: computed conditional formatting
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.2.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-10-21 02:39 UTC by rlk
Modified: 2016-03-28 15:44 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description rlk 2014-10-21 02:39:27 UTC
Request the ability to apply the format returned by a formula.  This is in essence like the STYLE() function, except applied via conditional formatting rather than by explicit use of STYLE().

Use case: applying a style to manually entered data.  For example, a formula that returns a named style based on the year of an entered date (perhaps by means of LOOKUP()) would have the style applied by conditional formatting to the entered cell.

The date method of conditional formatting is not appropriate for this case; the basic conditional formatting could be used, but only up to 10 years (or other ranges).  It's also less flexible, because the formula to be used for the computed conditional format could actually be a name; editing the value of the name changes the formula, which would allow the computed style to be changed easily everywhere.
Comment 1 Joel Madero 2015-10-16 03:24:23 UTC
Hi RLK -

So sorry for the long delay - I think in this case it's largely due to a lack of understanding about the use case for this enhancement request. Would you be so kind as to explain a bit clearer what exactly you're looking to accomplish. I vaguely understand the use case but not enough to mark this as NEW. I'm going to mark this as NEEDINFO - if you could just explain (in really simple terms....speak as if the QA member has no idea what you're referring to...like STYLE() function...no idea what that is).

Once you explain a bit more mark as UNCONFIRMED and we'll try to at least confirm this.

Again, so sorry for the delay moving this forward.

Lastly - as you probably know, this is an open source project so even if it's confirmed as a valid request, it might or might not be implemented (depends on a volunteer taking interest).
Comment 2 rlk 2015-10-16 12:54:15 UTC
A style is a collection of cell settings, including font, background color, border, etc.  Styles are each named; the F11 key opens the style editor, where you can create, modify, and apply styles.  So I could for example define a style named "Emphasis" with a bold, red font and 2 point border.

The STYLE() function can be used in formulas to apply a style to a cell.  So I could have a formula

Example 1:

=A1+STYLE("Emphasis")

that sets the cell to the value of cell A1 and applies the "Emphasis" style to it.  The argument to the STYLE function can be computed, just like any other.  One idiom I use a lot is

Example 2:

=A1+STYLE(LOOKUP(CURRENT(),Ranges,Styles))

where CURRENT() is the function returning the current value of the computation, Ranges is an array of values, and Styles is an array containing a list of style names.  The expression can be even more complex than that.

That all works fine for a cell containing a formula.  If the cell contains a value that the user enters, this doesn't work.  The only way to do variable formatting in a value cell is with conditional formatting.

Conditional formatting is done via a list of conditions and styles.  There can be up to 10 conditional styles in the list.  A condition can be either a simple test or a formula, but the formula returns either true or false.  The style corresponding to the first true condition is applied.  There are a few other ways of doing conditional formatting, such as color scales, but those don't work for what I want to do.

What I want to do is have a new kind of conditional formatting where the "condition" is a formula that returns the name of a style, in the same manner as examples 1 and 2.  Fixed conditions are too unwieldly for this purpose; it's limited to 10 expressions, and if I want to add in another slot, I have to redo all of the conditions.
Comment 3 GerardF 2015-10-16 13:32:43 UTC
If I understand correctly what you want to do (English is not my mother tongue), you can use the STYLE function in the conditional formatting functionality :

Choose the option "Formula is" and use STYLE(LOOKUP(A1,Ranges,Styles))
Comment 4 Buovjaga 2015-10-19 10:16:25 UTC
(In reply to GerardF from comment #3)
> If I understand correctly what you want to do (English is not my mother
> tongue), you can use the STYLE function in the conditional formatting
> functionality :
> 
> Choose the option "Formula is" and use STYLE(LOOKUP(A1,Ranges,Styles))

rlk: is this enough for you?

Set to NEEDINFO.
Change back to UNCONFIRMED, if it is not enough. Change to RESOLVED INVALID, if it does what you want.
Comment 5 rlk 2015-10-19 12:13:01 UTC
If that works, I believe that would do what I want.
Comment 6 rlk 2015-10-21 14:52:27 UTC
Confirmed that it works as desired.  Not that intuitive, but then again, the STYLE function isn't that intuitive to begin with.
Comment 7 rlk 2015-10-21 18:12:20 UTC
There's still a problem, though; at least in some situations, use of this technique results LibreOffice (both 4.2.7 and 5.0.2) using close to 100% of a CPU even if everything's computed and it's waiting for input.  It doesn't freeze the interface, although there does appear to be a slowdown, but it shouldn't be doing that.  I wasn't able to reproduce it with a small, simple case, but would you prefer I use this request or open a new one for that?
Comment 8 Buovjaga 2015-10-21 18:13:15 UTC
(In reply to rlk from comment #7)
> There's still a problem, though; at least in some situations, use of this
> technique results LibreOffice (both 4.2.7 and 5.0.2) using close to 100% of
> a CPU even if everything's computed and it's waiting for input.  It doesn't
> freeze the interface, although there does appear to be a slowdown, but it
> shouldn't be doing that.  I wasn't able to reproduce it with a small, simple
> case, but would you prefer I use this request or open a new one for that?

Please open a new report. Thanks!
Comment 9 rlk 2016-03-28 15:44:47 UTC
Comment added to bugt 95233:

What I usually do for cells with formulas is

=a1+b2+STYLE(LOOKUP(CURRENT(), FooRanges, FooStyles)

where FooRanges and FooStyles might be adjacent columns that look something like this (this is an actual example -- number of meters I've rowed in a session, to be precise):

0	blank
1	slow
1000	default
2000	slow1
4000	200
5000	158
6000	155
7000	1525
8000	151
10000	150
12000	149
15000	148
17000	147
20000	146
21097	145nb
22000	143
23000	fast
24000	fast1
25000	fast2
26000	fast3
27000	fast4
28000	fast5

I'd like to be able to do this for text entry cells, but easily change the breakpoints (which are used elsewhere) without having to change both the lookup table and the conditional formatting (which won't even work in this case due to the number of ranges).

Reopening 85264 would be one way of handling this.