0

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

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
Pat Ryan
  • 5
  • 2
  • 4

1 Answers1

1

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.

BrakNicku
  • 5,935
  • 3
  • 24
  • 38
  • Thank you. That works!! I didn't know about the NA() function so that would never have occurred to me. I really appreciate that! – Pat Ryan Mar 30 '15 at 19:25