Stoplight (traffic light questions)

Question

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

Answer 1

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")

Here is more information about CHOOSE function.