1

If the stoplight produces a 0 value how do you get it to appear blank for a zero value? Right now it returns this: #VALUE!. Using the formula below how do you get the formula to produce a stoplight for a double digit value: I was looking to get it to generate black on the number 13 and white on the number 15?

="<DIV style='font-weight:bold; font-size:45px; text-align:center; color:"&CHOOSE(RIGHT(LEFT(CASES,2),1),"green","red","yellow","orange","blue","purple","grey","black","white")&";'>•</DIV>"
  • ="<DIV style='font-weight:bold; font-size:45px; text-align:center; color:"&CHOOSE(RIGHT(LEFT(CASES,2),1),"green","red","yellow","orange","blue","purple","grey","black","white")&";'>•</DIV>" – vier111 Aug 2 '17 at 22:04
2

Check the CHOOSE function index parameter to avoid getting #VALUE! error.

="<DIV style='font-weight:bold; font-size:45px; text-align:center; "&IF(RIGHT(LEFT(CASES;2);1)<=0;"color:white";"color:"&CHOOSE(1;"green";"red";"yellow";"orange";"blue";"grey";"black";"white"))&";'>•</DIV‌​>"

Since you don't have 15 values, you will get an error if you try to use CHOOSE function like

CHOOSE(15;"green";"red";"yellow";"orange";"blue";"grey";"black";"white")

You should use it with 7 for black because black is the 7th element.

CHOOSE(7;"green";"red";"yellow";"orange";"blue";"grey";"black";"white")

enter image description here

Here is more information about CHOOSE function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.