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

Avadh wrote:
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


Is this an Excel question, and if so, how?

On Jul 15, 11:52 am, Avadh wrote:
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 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

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(),"gr een")

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.

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