I run a pool league and I have a chart of player handicaps over time. Handicaps change over time based on performance. Since not all matches are played on the same days, I chart against the ordinal value (1st match, 2nd match, etc.) rather than against time.
My problem is with the missing values at the end of each series. Excel plots my lines crashing to zero, making for an ugly (and wrong) chart. This is true whether I have a zero or a blank in the 1st unplayed match of each series.
Zeros within a range can be shown as a gap, but the zeros at the end of a series will not show as gaps.
I know that I can show #NA in the empty cells and they then will not display, which is what I want in the chart, but then my table of values is ugly, filled with #NAs for all unplayed matches.
How can I get both a chart with series which just stop at the last nonzero value and cells which show as blanks in the table for zero values? I'm already displaying zeros as blanks but that doesn't fix it.
All data cells contain the same formula.
=IF(something,value,"")
(I didn't look at your file). The "" looks blank, but Excel will plot it as a zero value. Replace "" with NA():=IF(something,value,NA())
, which places #N/A in the cell but which isn't plotted in the chart.