Re: Viewpoints on conditional formatting, anyone?

[Jody Goldberg <jody gnome org>]

On Mon, Jan 06, 2003 at 09:58:50PM +0100, Jon-Kare Hellan wrote:
> Conditional formatting is a much requested feature which Excel,
> OpenOffice and KSpread have. It lets you specify e.g that cells with
> the string "blue" should appear red, and there are many, more
> sensible, applications.

Should note a few things before starting discussion so the mailing
list record is clear.

1) Gnumeric currently supports the basic XL95 style conditions in 
   value formats.  This allows the foreground colour to change based
   on the current cells value.

2) Implementing a larger set of conditional attributes is relatively
   painless in the current architecture.  JonKare has already dealt
   with most of the pain associated with style having sheet
   references as part of the autocolour support.

3) XL import/export for conditionals is only partially done.
   Unfortunately, the records are undocumented and I'm fairly sure
   OO is wrong.  So there is some research to do, but nothing
   insurmountable.

> Excel is limited to up to 3 conditions. Is there a need for more, and
> can this be done without making the GUI confusing?
Yes and no.  Using XLs current cascading semantics for the styles
(and we will need to support that no matter what we choose) means
that we need at least 3.  There is no real implementation cost to
making that an arbitrary number.   There may be performance
implications for large numbers of layers, but then that is the users
choice.  However, I'd like to consider _why_ someone would want more
than 3 layers.  The interactions between layers is fairly poor, in
that they appear to just apply in order.  My bet is that what people
want in many cases with large condition sets is not a _conditional_.
They want  _calculated_.  XL conditionals are effectively structured
as

    for (i = 1 ; i <= 3; i++)
        if (expr[i])
            apply format[i];

If what a user really wants is to colour code the background of a
cell based on a table of value (a common use-case) then they are
forced to create a pile of tiny formats, each specifying a single
attribute.    I'd prefer to see an extension of the form
    background colour == expression
You loose pretty selectors for the colour, but I think the
localisation of the the result to a single page makes up for it.

> Excel's GUI is dynamic, it starts with a frame for a single condition
> and lets you add more as you need them, and delete when you change
> your mind. OpenOffice always shows all 3, and has a check box to
> indicate which ones are actually used. I like OpenOffice's way, but it 
> won't work for a large or unlimited number of conditions.
A fixed number carries with it the cost of supporting an 'unused'
flag in the ui and as you point out does not scale.  I'd probably go
with an XL style (similar to the sort dialog or the function guru).
I don't have solid impressions of whether a fixed upper limit on the
number of conditions is necessary.