1

I'm doing a weekly log for some athletes with some data and I want to chart it as they fill it in during the week. The only issue is that for days in the week where the data hasn't been entered yet, the formula produces a result of zero, which gets included in the chart, throwing the scatter graph off. How can I get the chart to stop plotting zeros?

fixer1234
  • 28,064
Rob
  • 11

1 Answers1

2

Adjust your formula so that if the input is blank, it returns NA(). Something like this:

=IF(LEN(A2)>0,your-formula-here,NA())

This puts #N/A into the cell, which may look strange, but an XY chart ignores #N/A, without plotting a point. If the chart has lines connecting the markers, the line will pass over where the #N/A point would have gone, connecting the points on either side.

Jon Peltier
  • 4,630
  • 24
  • 27