In a Balanced Scorecard we use several indicators that are in a binomial or
either/or form: complete or incomplete; on-time or behind, etc. We use
charts in Excel to display other forms of indicators but are having trouble
coming up with a good graphic way of displaying this kind of data.
Any ideas?

Why not use conditional formatting to color the cells to an appropriate color
based on the indicator? Access Conditional formatting using FORMAT -
Conditional Formatting.

"stevewoodall" wrote:

In a Balanced Scorecard we use several indicators that are in a binomial or
either/or form: complete or incomplete; on-time or behind, etc. We use
charts in Excel to display other forms of indicators but are having trouble
coming up with a good graphic way of displaying this kind of data.
Any ideas?

Did you try pie charts.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377713

A few ideas come to mind.

Use a pie chart as has already been suggested. This will have two
slices, one for the # of zeros, the other for the number of 1s.

Use a stacked bar chart. Put the #1s in green at the bottom. Put the
(#zeros - #1s) on the top in red.

Use conditional charts. Suppose you have thresholds for #on-time, for
example. <80% is really bad 80-90% is OK, 90% is good. Suppose you
have % #on-time in B3. Then, in C3 enter =IF(B3<=0.8,B3,NA()) In D3
enter =IF(AND(B30.8,B3<=0.9),B3,NA()) and in E3 enter =IF(B30.9,B3,NA
())

Now, plot B3:D3 in a stacked column chart (plot by column). Format the
the first series red, the 2nd yellow, and the third green.

[Somewhere on one of my computers is an XL file that has conditional
charts with gradual color transitions from red (at the bottom) to
yellow (middle) to green (the top) for a balanced scorecard. Just
don't remember which one.]
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
In a Balanced Scorecard we use several indicators that are in a binomial or
either/or form: complete or incomplete; on-time or behind, etc. We use
charts in Excel to display other forms of indicators but are having trouble
coming up with a good graphic way of displaying this kind of data.
Any ideas?

Yes. However, I wasn't able to find a way to do a legend this way. Using the
X axis for the time frame (quarters) and the Y axis to illustrate an
"either-or" indicator. Is there a way to convert a numerical indicator or
text (ie, "red", "green" or "yellow") to a pie chart color?

"mangesh_yadav" wrote:


Did you try pie charts.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377713

Thanks for that. Did it. Now the cells in the spreadsheet are colored. How do
I convert that into a pie chart?

"Barb R." wrote:

Why not use conditional formatting to color the cells to an appropriate color
based on the indicator? Access Conditional formatting using FORMAT -
Conditional Formatting.

"stevewoodall" wrote:

In a Balanced Scorecard we use several indicators that are in a binomial or
either/or form: complete or incomplete; on-time or behind, etc. We use
charts in Excel to display other forms of indicators but are having trouble
coming up with a good graphic way of displaying this kind of data.
Any ideas?

What if, to use your illustration, B3="on-target"; or "delayed"; or
"post-poned" or "no data"? I am wanting to convert each term (from the drop
down list) to a color in a simple pie chart, with one series representing
four quarters of the year--or 12 months of the year. So each quarter would
color code to one of the above options.

"Tushar Mehta" wrote:

A few ideas come to mind.

Use a pie chart as has already been suggested. This will have two
slices, one for the # of zeros, the other for the number of 1s.

Use a stacked bar chart. Put the #1s in green at the bottom. Put the
(#zeros - #1s) on the top in red.

Use conditional charts. Suppose you have thresholds for #on-time, for
example. <80% is really bad 80-90% is OK, 90% is good. Suppose you
have % #on-time in B3. Then, in C3 enter =IF(B3<=0.8,B3,NA()) In D3
enter =IF(AND(B30.8,B3<=0.9),B3,NA()) and in E3 enter =IF(B30.9,B3,NA
())

Now, plot B3:D3 in a stacked column chart (plot by column). Format the
the first series red, the 2nd yellow, and the third green.

[Somewhere on one of my computers is an XL file that has conditional
charts with gradual color transitions from red (at the bottom) to
yellow (middle) to green (the top) for a balanced scorecard. Just
don't remember which one.]
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
In a Balanced Scorecard we use several indicators that are in a binomial or
either/or form: complete or incomplete; on-time or behind, etc. We use
charts in Excel to display other forms of indicators but are having trouble
coming up with a good graphic way of displaying this kind of data.
Any ideas?

Can you give an example of your data.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377713

Yes, it's very simple stuff.

Four columns, each representing a Quarterly Reporting Period (first qtr;
second qtr;)

35 rows of data, some of which is numerical (in which case we use simple bar
charts), some of which are basically text entries from drop down options.
Examples would be "Complete/Incomplete", or "On-time"; "Delayed";
"Cancelled"; "No data"

I've been illustrating these with pie charts and can make them look ok with
lots of individual tweaking and without a legend. I'd like to find a more
efficient way to do it (just added the drop down lists) that will allow me to
generate a proper legend.
Thanks for your interest.




"mangesh_yadav" wrote:


Can you give an example of your data.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377713