CALC: Bug in/because of function STYLE (German VORLAGE)?

[michaelof]

It seems, that a conditional formatting via formula STYLE(…) leads to unpredictable comparisons.

Example uploaded: SampleSTYLE.ods

• cell B1 and A2 both contain number value “4”

• cell B3 contains (german) formula: =STYLE(IFS(A2<B1;"Grün";A2=B1;"Gelb";A2>B1;"Rot")) → works fine, as it should

• cell B2 contains (german) formula: =A2+STYLE(IFS(CURRENT()<B1;"Grün";CURRENT()=B1;"Gelb";CURRENT()>B1;"Rot"))

As CURRENT has value 4, as B1 has, condition “CURRENT()=B1” should be met, but isn’t!

• It seems, that “VALUE” and “VALUE plus formatting via STYLE()” seems to be NOT THE SAME when used in comparison.

Is this a bug? Or is there any chance to workaround it?

[Regina]

That is not a problem with STYLE but with IFS. Compare these

1. Solution with IFS =A2+IFS(CURRENT()<B1;1;CURRENT()=B1;10;CURRENT()>B1;100) results in 104 (wrong).
2. Solution with nested IF =A2+IF(CURRENT()<B1;1;IF(CURRENT()=B1;10;100)) results in 14 (correct).

So workaround: Do not use IFS in combination with CURRENT, but use nested IF.

[michaelof]

Hi Regina,

Thanks for the workaround, works perfectly!

But what do you think regarding my bug question? Do you agree that IFS/WENNS is buggy?

[Regina]

Yes, I think it is a bug, that IFS does not work together with CURRENT.

[michaelof]

Bug opened: https://bugs.documentfoundation.org/show_bug.cgi?id=111675

[JohnSUN]

Workaround? OK, try this

=A1+STYLE(CHOOSE(SIGN(CURRENT()-B1)+2;"Rot";"Gelb";"Grün"))

[michaelof]

Nice idea with choose, but does not fit for my requirement, as B1 and A2 might be any value in final spreadsheet. “Rot”;“Gelb”;“Grün” = red/yellow/green should be traffic light indicators, to visualize if A2 is at or above the limit B1.

Thank you !!