CODEWITHSUNDEEP
excelcharts
Pat Ryan
Pat Ryan

Reputation: 17

How to make an Excel line chart ignore certain cells?

I have monthly values in a column with cumulative values in the adjacent column. The cumulative calculation should only work where there is data for that month so I have used formulas such as =IF(E6>0,F5+E6,"")

I need a line chart showing the cumulative results but I don't want Excel to plot the cells containing "" Is this possible, using either chart options, or by changing the cumulative formula?

I've tried to post a picture of my chart but I'm new here so it won't allow me to. Let's see if it allows a link to mediafire instead.

In my picture (if you can see it) I want the red line for 2015 to stop at March, instead of plotting zeroes for the rest of the year.

Link to screenshot

Upvotes: 0

Views: 2849

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

One possible solution is to use:

 =IF(E6>0,F5+E6,NA())

It will display #N/A instead of empty string. If it doesn't look OK, you could create a helper column with this formula, add it to chart, and hide this column.

Upvotes: 1

Related Questions