How to add traffice light

  • Thread starter Thread starter Avadh
  • Start date Start date
A

Avadh

Hi,

I am looking for a solution to add the 2 traffic lights so that get a
Cumulative status.

For eg : red + amber is red
red + green is red
amber + green is amber
red+red is red
amber + amber is red
green + green is green

Thanks in advance,
Avadh
 
Hi,

I am looking for a solution to add the 2 traffic lights so that get a
Cumulative status.

For eg : red + amber is red
red + green is red
amber + green is amber
red+red is red
amber + amber is red
green + green is green

Thanks in advance,
Avadh
Click to expand...

Hi Avadh,
in my eyes it's a question of upper and lower limits.
a.) define upper and lower limits for each traffic light color; assign
the color for all data in these ranges.
b.) define rules for cumulative traffic lights, i.e. cumulation of
these tolerance ranges.

Have fun, cheers
Michael
 
I am looking for a solution to add the 2 traffic lights so that get a
Cumulative status.

For eg : red + amber is red
red + green is red
amber + green is amber
red+red is red
amber + amber is red
green + green is green
Click to expand...

Though I'm not sure what "cumulative status" means in this context, let me
address the color combinations that are specified.

If the two text values are in A1 and B1, the following formula gives the
specified result:

=CHOOSE(((A1="red")+2*(A1="amber")+3*(A1="green"))*
((B1="red")+2*(B1="amber")+3*(B1="green")),
"red","red","red","red",NA(),"amber",NA(),NA(),"green")

Explanation: The formula counts red as 1, amber as 2, and green as 3. Then
it multiplies the two numbers. The product is between 1 and 9, but cannot
be 5, 7, or 8. The CHOOSE(...) returns the specified result for each of the
color pairs. (Multiplication works here but not addition, because 4 = 2+2 =
3+1 is ambiguous.)

Modify to suit.
 
Multiplication works here but not
addition, because 4 = 2+2 = 3+1 is ambiguous.
Click to expand...

On second thought, addition can work, too. Here's a variation using
addition:

=CHOOSE((A1="red")+2*(A1="amber")+4*(A1="green")+
(B1="red")+2*(B1="amber")+4*(B1="green"),
NA(),"red","red","red","red","amber",NA(),"green")
 
Back
Top