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

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

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?

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 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 <599B8FE0-27D5-48F2-B17B-D59754C70EDD@microsoft.com>,
stevewoodall@discussions.microsoft.com 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(B3>0.8,B3<=0.9),B3,NA()) and in E3 enter =IF(B3>0.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 <599B8FE0-27D5-48F2-B17B-D59754C70EDD@microsoft.com>,
> stevewoodall@discussions.microsoft.com 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

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