Ask Your Question

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

asked 2017-08-08 05:54:26 +0100

michaelof gravatar image

updated 2017-08-08 06:10:58 +0100

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-12 18:21:16.163634

2 Answers

Sort by » oldest newest most voted

answered 2017-08-08 16:52:32 +0100

Regina gravatar image

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.

edit flag offensive delete link more


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?

michaelof gravatar imagemichaelof ( 2017-08-08 22:13:41 +0100 )edit

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

Regina gravatar imageRegina ( 2017-08-09 02:03:30 +0100 )edit

answered 2017-08-08 07:08:44 +0100

JohnSUN gravatar image

updated 2017-08-08 07:10:30 +0100

Workaround? OK, try this

edit flag offensive delete link more


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

michaelof gravatar imagemichaelof ( 2017-08-08 22:12:03 +0100 )edit

Question Tools

1 follower


Asked: 2017-08-08 05:54:26 +0100

Seen: 55 times

Last updated: Aug 08 '17