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>"
Improve this question
1
  • ="<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>" Commented Aug 2, 2017 at 22:04

1 Answer 1

Reset to default
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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.