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

Jeeped, this worked wonderfully for me - as long as you don't get intimidated by array formulas, this is a great, dynamic solution.  Well done!