Executive Summary Dashboard with traffic lights

amoten84

New Member
Joined
Mar 28, 2014
Messages
4
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.

Dashboard.jpg
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
829
Are you using conditional formatting? In 2010 there is an option to use such traffic light system.
 

hode602

New Member
Joined
Mar 27, 2014
Messages
4
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.

Dashboard.jpg
 

amoten84

New Member
Joined
Mar 28, 2014
Messages
4
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

New Member
Joined
Mar 28, 2014
Messages
4
I am already using the traffic light, just need help now in assign scores for each light on the adjacent column.
 

amoten84

New Member
Joined
Mar 28, 2014
Messages
4
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,617
Messages
5,597,196
Members
414,131
Latest member
Balakrishnan2027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top