How to stop Pie Chart from using N/A# for values
 

Hello,

I have 2 columns: Column 1 is customer name. Column 2 is sales for the
period.

If a customer had no sales for the period the sales column will either be
N/A# or zero. When I plot in a pie chart, I only want to show customers who
had sales for that period.

Is there a way to only plot those customers with sales without having to
change the range and sort every period?

Customer Sales
Cust1 $100
Cust2 0
Cust3 $50
Cust4 N/A

The pie chart data labels show the customer regardless of sales and if I
blank out the customer, the value data label still shows 0 or N/A.

thanx for any direction.

db

How to stop Pie Chart from using N/A# for values
 

Try using auto filter on the sales to show everything over 0. This will get
rid of the zero's and n/a, and create the chart from that data. If you need
to remove the filter but keep the filtered chart you will need to copy it
into word as an image and then copy it back to excel.
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Dave" wrote:

Hello,

I have 2 columns: Column 1 is customer name. Column 2 is sales for the
period.

If a customer had no sales for the period the sales column will either be
N/A# or zero. When I plot in a pie chart, I only want to show customers who
had sales for that period.

Is there a way to only plot those customers with sales without having to
change the range and sort every period?

Customer Sales
Cust1 $100
Cust2 0
Cust3 $50
Cust4 N/A

The pie chart data labels show the customer regardless of sales and if I
blank out the customer, the value data label still shows 0 or N/A.

thanx for any direction.

db

How to stop Pie Chart from using N/A# for values
 

Thanks for the input. This was for a user who isn't very good with Excel.
In one column I used an IF statement to pull customer names who had greater
than zero sales then in the next column I wrote a custom function that
creates a list of unique customers (igores blanks and errors) and adds the
number of customers found at the top of the array. I then used SUMIF to get
their total sales next to their names.

For the chart(s) I used a dynamic range that is based on the number of
unique customers I found thru my custom function. The range grows or
shrinks based on that number (I used the Offset function when defining the
range). A little too complicated for this user so I said "just use it -
it's magic."

db
"john" wrote in message
...
Try using auto filter on the sales to show everything over 0. This will
get
rid of the zero's and n/a, and create the chart from that data. If you
need
to remove the filter but keep the filtered chart you will need to copy it
into word as an image and then copy it back to excel.
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"Dave" wrote:

Hello,

I have 2 columns: Column 1 is customer name. Column 2 is sales for the
period.

If a customer had no sales for the period the sales column will either be
N/A# or zero. When I plot in a pie chart, I only want to show customers
who
had sales for that period.

Is there a way to only plot those customers with sales without having to
change the range and sort every period?

Customer Sales
Cust1 $100
Cust2 0
Cust3 $50
Cust4 N/A

The pie chart data labels show the customer regardless of sales and if I
blank out the customer, the value data label still shows 0 or N/A.

thanx for any direction.

db