Graphic depiction of an "either-or" (0 or 1) indicator

[stevewoodall]

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?

 

[Barb R.]

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

 

[mangesh_yadav]

Did you try pie charts.

Manges

 

[Tushar Mehta]

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(B3>0.8,B3<=0.9),B3,NA()) and in E3 enter =IF(B3>0.9,B3,NA
())

Now, plot B33 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

 

[stevewoodall]

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?

 

[stevewoodall]

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

 

[stevewoodall]

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.

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(B3>0.8,B3<=0.9),B3,NA()) and in E3 enter =IF(B3>0.9,B3,NA
())

Now, plot B33 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 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?

 

[mangesh_yadav]

Can you give an example of your data.

Mangesh

 

[stevewoodall]

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.