How to avoid charting "" values in a line chart

[bigpat]

Hi,

I have monthly data for a year and a bit, with cumulative calculations in each year. Please see below for the cumulative calculation. It uses an IF formula all the way down the table, to return "" for months that have not yet elapsed.

I need to use a line chart to compare the cumulative values in each year, but I don't want to plot the "" values. I don't yet have any data for April 2015 so I need that line to stop at March, rather than dropping to zero for the rest of the year. I have Excel 2010 and I have set Hidden and empty cells setting to "Not plotted". But it doesn't work, because I guess "" does not count as empty.

I could remove the cumulative formula from the months that haven't happened yet and add it back in once a month, a cell at a time, but I have a lot of similar sheets to do and I need to get it all set up in advance so I can hand it over to someone else.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Data[/TD]
[TD]Cumulative[/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Cumulative[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]25[/TD]
[TD]45[/TD]
[TD][/TD]
[TD]34[/TD]
[TD]59[/TD]
[TD]<-- Formula =IF(D4>0,E3+E4,"")[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]23[/TD]
[TD]68[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]42[/TD]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]12[/TD]
[TD]122[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]32[/TD]
[TD]154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]14[/TD]
[TD]168[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD]34[/TD]
[TD]202[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sept[/TD]
[TD]23[/TD]
[TD]225[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD]53[/TD]
[TD]278[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD]12[/TD]
[TD]290[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]34[/TD]
[TD]324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So: could I use a different formula to make the cumulative cell empty if there is no data for that month? Or is there some other chart setting that will not plot, or hide, the "" values?

Thank you

 

[SGeorge123]

You could change your if function to give any 0's a "N/A!" result - these don't plot in graphs

You could edit your formula to read:
=IF(D4>0,E3+E4,NA())

I hope this helps?

 

[bigpat]

That's brilliant. I never knew there was an NA function and wouldn't have known what to do with it if I did. This works perfectly, especially as I can use conditional formatting to hide #N/A with white font.

Thank you so much!!