How do I exclude zero values from a chart without creating a new column of data?

Question

I use the cubevalue function to essentially create a new pivot table customized for our customers' information. I take each column of data in the customized table and chart it as a line graph. In a number of cases a parameter can change (like year) and new values are filled in the column.

If there are a number of leading or trailing zeros in the data, I want the chart to exclude them. Is there a way for Excel to handle this?

For example, my data:

0

0

0

1

4

6

3

6

4

2

0

0

0

0

I would only want the data charted that wasn't 0 (so {1, 4, 6, 3, 6, 4, 2} would be charted). But I do not want to set the range to only include those values because if a parameters changes and the values in the column change, I want the chart to be able to chart the new values automatically.

Any way to do this without a macro? I'm not allowed to write macros for this particular problem.

Thanks for your help.

Answer 1

You could substitute the 0's with #N/A but that will still produce blank placeholders in your chart. It would seem that you need to squeeze the non-zero values out of the value list and chart those. If your list of numbers is in A2:A15, put this array formula into B2,

=INDEX($A$2:$A$15,MATCH(0,IF($A$2:$A$15<>0,COUNTIF(B$1:B1,$A$2:$A$15),1),0))

Finalize it with Ctrl+Shift+Enter rather than simply Enter and fill down to B15.

Next, select just B2 and open the Formulas, Name Manager. Create a new name called something like My_Chart_Values and use the following for the Refers to:

=OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B$2:$B$99),1)

This will dynamically set the My_Chart_Values named range according to how many non-#N/A values are in B2:B15.

Now go into your chart and use Select Data on your columns. Since a chart can be moved just about anywhere, you will have to include the workbook name in the Series values: definaition. My example was Book11.xlsm so i used,

=Book11.xlsm!My_Chart_Values

Click OK and you should have a dynamically generated column chart.

More on this at:

Using named ranges to create dynamic charts in Excel

Answer 2

My apologies. I've just realized that you had two 4 values in that list and my extraction was taking a unique list (one of the 4s was dropped). Post back if this is a problem with your real-world data and I'll try to come up with an alternate formula for pulling the non-zero values.

Answer 3

Hi,

One way would be this

1. Create your graph
2. Apply an auto filter on the dataset and uncheck 0 in the filter drop down box
3. As the source data changes, just click on Data > Reapply to shown additional non zero rows or hide zero rows

Answer 4

The only problem is I can't "sort" the data so to speak. Let me try to explain my data a little bit better.

I have 10 columns, each with values retrieved using the cubevalue function. The parameters I use for the cubevalue function are chosen from a drop down list of parameters available (I set these up as names in the name manager under Formulas). So, for example ...

            2000   2001 ...

2001     0          0

2002     9          0

2003     3          4

2004     2          3

2005     5          4

2006     3          5

What this data stands for are forecasted amounts in a particular year. I want to graph each year's forecasts against each other to illustrate how the values have changed.

So, the data above would be in year 2000 we forecasted the values {0, 9, 3, 2, 5, 3} for the next 6 years, but in 2001 we forecasted the values {0, 0, 4, 3, 4, 5}.  However, the 2000 and 2001 are used to lookup those values and the user can change the 2000 to 2010, which would mean all of the values in the column would then change to the forecasted values from 2010.

I don't know if Excel can do what I'm wanting done without writing a macro, or manually changing the range of the chart each time the data is changed.

I don't know if that made any more sense, but sorting isn't an option or the forecasted values are out of order.

Answer 5

If you can redact the workbook sufficiently and upload a copy of your workbook to a public file area like SkyDrive, DropBox  or Google Drive and post the location to the publicly shared file back here. Being able to see the full data set and how the user selects the year to chart seems to be the most expedient method of arriving at a solution.