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

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?

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.

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?

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

Bug opened:

Workaround? OK, try this


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 !!