3rd Axis Line Graph Overlay Data Set Problem

**joe.teixeira** · 10-04-2011, 06:54 PM

I have attached a stacked and clustered bar chart that otherwise looks fine. I wanted to overlay a Percentage data set from the right axis. However, due to the spacing of the four accounting data sets, the percentage line graph has many data points with the value of zero as you can see in the data attachment.

Please help me either remove the 0% data points or a better way to create a stacked cluster bar chart.

Thanks in advance.

Joe

**Andy Pope** · 10-05-2011, 03:55 AM

First change the chart type of the line from stacked line with markers to line with markers.

Use Select data dialog to set Hidden/Empty cells as gap rather than zero.

If you want an actual line rather than just data points you change use this formula in column O of the data source.

=IFERROR(SUM(M33/N33)-1,NA())

**joe.teixeira** · 10-10-2011, 07:36 PM

Thanks Andy. I never solved my problem (with Excel at least). I had already Hidden empty cells as gap and I used the iferror equation you listed. But alas, I had to get it off of my desk and into the hands of the other unsolvable problem, my boss.

Thanks again Andy.