Tracking and charting registered products

[darkwing_duck]

I have a very simple spreadsheet that has a column of serial numbers
and next to it a column with a yes/no tracking whether a serial number
has been registered or not. I want my sales team calling into an
account once 50% of the serials are registered to sell more so I
created a pivot table that simply has the yes/no field on the top and
a count of each in the data. Then I created a pivot chart with the
yes and no next to each other and the data labels displayed. So far
so good and this does accomplish what I want at it's most simple
level.

What I would love to do though is:

1) change the count (and therefore the data label) to a percentage of
the total - so right now my test spreadsheet has 5671 in the yes
column and 4329 in the no column. I'd like to have those reflect
56.7% and 43.3% instead of the hard numbers

2) display "something bold" across the chart when the yes column
exceeds 50%. For instance, a big bold "CALL IN" across the chart or
perhaps a macro that inserts a pic on top of the chart when the yes
column hits 50%.

TIA,
Robert

 

[John Mansfield]

To change the pivot chart from showing totals to a percent of total,
double-click on the "sum of" option which should be on the top-left side of
the chart. In the pivot table field dialog box, go to Options -> Show Data
As -> % of Total. Hit the OK key.

To display a bold statement across the top of the chart, create a text box
within the chart and link a formula to it. The formula should be tied to
your pivot table "Yes / No" results. An example might look like this:

=IF(GETPIVOTDATA("Serial",$A$3,"Registered","Yes")>0.5,"Call Customer"," ")