# Excel Chart with Different Series Lengths

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.

Sample data and chart here

All data cells contain the same formula.

• It would better if you can attach some sample data in a worksheet and describe what you have done and what you wanted. – steviG Aug 13 at 14:35
• @JohnBiddle - the problem is your formulas probably are like `=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. – Jon Peltier Sep 8 at 3:32
• @jon Peltier See question paragraph 4. – John Biddle Sep 10 at 1:50
• Sorry, I skimmed right over that. What you can do is use conditional formatting, based on a formula, and use a formula like =ISNA(cell reference). If true, change the font color of the cell to white to match the background of the cell. This is the first example in Excel Conditional Formatting Examples. – Jon Peltier Sep 10 at 12:40