Need Chart's Line to Skip Zero Values

**ComcoDG** · 10-13-2008, 03:00 PM

Hi all,

[Please see the attachment for description below.]
I’m trying to create a graph which tracks our production. I have a USER SHEET which employees use to enter their daily production and which can be filtered to show only the month the company is currently working on. Because the filter would remove cells needed for each months chart, each chart works from another unfiltered set of fields on a separate sheet called REPORTS SHEET which replicates the data from the USER SHEET. There are some days which are blank and which I need to be able to see in the database, but which I don’t want to show in the chart. (An example of this is the 4th of October.) Currently the values for the replicated blank cells are showing as zero on my chart because of the function located in that cell.

Does anyone know how to get the chart’s line to skip over the blank values so there will be just a smooth transition from one cell containing a value to the other? I have attached a spreadsheet incase some brave soul has any ideas for me.

Thank you so much for the help!

**protonLeah** · 10-13-2008, 11:39 PM

Right click your charts, select Chart options, Axes and finally Category instead of Automatic. The bar chart looks neater for your data.

**Andy Pope** · 10-14-2008, 04:45 AM

Change your formula on the Reporting sheet.

B2: =IF('User Sheet'!C2<>"",'User Sheet'!C2,NA())

Using "" instead of NA() will result in the zero as any text in the chart data series is treated as zero.
The NA() will stop the data marker from being plotted. The line however will be plotted between valided data points.

**ComcoDG** · 10-14-2008, 10:45 AM

Thanks for the responses and for the ideas.

The NA() text works for a single cell, but unfortunately it returns error for all days not yet completed. This is a problem when calculating the average production, or if the first cell in the chart’s source data is left blank by the user. For example, in the attached you will see that the month of September works fine when the users doesn’t leave blank cells, but October runs into errors for blank cells as does November. I’ve look through the varying results and can’t seem to see why some are returning the error.

It's so close!

**Andy Pope** · 10-14-2008, 11:07 AM

you need to adapt your formula to take into account the NA's

D2: =SUM(IF(ISNUMBER($B$2:$B$26),$B$2:$B$26))/COUNT(IF(ISNUMBER($B$2:$B$26),$B$2:$B$26))

This is an Array formula so commit with CTRL+SHIFT+ENTER

**ComcoDG** · 10-14-2008, 11:27 AM

Andy,

Your function works brilliantly! It even solves another annoying item which I had just made my peace with. Namely, the graph listed zeros for all days currently missing. Now only days are charted which have actually been completed.

I am not familiar with Array formulas and have some reading ahead of me.

Thank you for your help and for broadening my horizons!

David