Executive Summary Dashboard with traffic lights

[amoten84]

Hi Guys,

New to this forum need help with creating executive summary dashboard. I am using traffic light for quick visual, and now want to assign score to each row depending on the light.

For example:

Green = 12
Amber = half of green
Red = 0

The scoring will vary depending on the item. Any help will be greatly appreciated.

 

[Skybluekid]

Are you using conditional formatting? In 2010 there is an option to use such traffic light system.

 

[hode602]

Use if function like this: =if($A1>67|12|IF($A1>33|6|0)), that should give use the score

Hi Guys,

New to this forum need help with creating executive summary dashboard. I am using traffic light for quick visual, and now want to assign score to each row depending on the light.

For example:

Green = 12
Amber = half of green
Red = 0

The scoring will vary depending on the item. Any help will be greatly appreciated.

 

[amoten84]

Use if function like this: =if($A1>67|12|IF($A1>33|6|0)), that should give use the score

I would like to use cell reference than hardcode number. When I tried cell referencing it gave an error. Is it possible to just look at the light in the corresponding cell and then return a value?

 

[amoten84]

I am already using the traffic light, just need help now in assign scores for each light on the adjacent column.

 

[amoten84]

I created a separate score table and used this

=IF(C20<=C6,D6,IF(AND(C20>=C7,C20<=C8),D7,IF(C20>=C9,D9,0)))