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

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

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

Sort by » oldest newest most voted

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.

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?

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

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

( 2017-08-09 02:03:30 +0100 )edit
( 2017-08-11 02:01:34 +0100 )edit

Workaround? OK, try this

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

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

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