Ask Your Question
0

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

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

michaelof gravatar image

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

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 close merge delete

2 Answers

Sort by » oldest newest most voted
1

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

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

Comments

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 +0200 )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 +0200 )edit
0

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

JohnSUN gravatar image

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

Workaround? OK, try this

=A1+STYLE(CHOOSE(SIGN(CURRENT()-B1)+2;"Rot";"Gelb";"Grün"))
edit flag offensive delete link more

Comments

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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 39 times

Last updated: Aug 08 '17