- TRUE EMPTY CELL OPTION
The best way to handle this is a true and proper empty cell (no value, no formula), but understandably this isn't always practical with continuous running formulas per column.
This method has the added advantage (disadvantage for some?) of a discontinuous line in your normal scatter plot.
- FILTER "" BLANKS OPTION
Using ="" in your formulae will make your pivots work as expected. You can then add a normal filter to your data and filter out the blanks. The normal graph will ignore the hidden rows and plot a continuous line.
Unfortunately this option doesn't work if you have multiple series with "blanks" scattered unevenly across the rows.
- PIVOT REFRESH OPTION
You could also take advantage of the fact that pivot table don't automatically refresh. Name a cell "plotforpivot" and give it the value TRUE. Then use this formula for your "blank" cells:
Now refresh you pivot table. Next change the "plotforpivot" cell to FALSE. Your pivot and normal plot will now reflect correctly until the next pivot refresh.
- AGGREGATE function option
You could opt for calculating the average yourself (i.e. not using a pivot table). If you use NA() the normal AVERAGE function (also used by the Pivot) will return #N/A as well. However, the AGGREGATE function can be used to compute the average whilst ignoring errors like so
=AGGREGATE(1,7,B2:B7). Sadly there is no option to use AGGREGATE functions to summarise pivot values.
- FILTER THE PIVOT
Last but not least, you can filter the #N/A values in the pivot. Firstly use
=NA() for your blanks so that the normal graphs plot nicely. Next set up your pivot as you like, but also drag your series data in under the pivot "filter" section. Now click the down filter arrow next to the series at the top of the pivot table, click "select multiple items" and uncheck "#N/A". Unfortunately this suffers the same problem as filtering with option #2, as multiple series will also filter whole rows at a time. I guess fundamentally this is a problem, as Excel cannot average say 10 items for one series, and then only 9 items for another. It cannot hide just one cell, only a whole row or whole column. Perhaps you can do multiple pivot tables instead? One for each series?
In the end if you have multiple series you might have no other option than to duplicate each series column, one with #N/A and the other with "". You can at least do this easily starting with say NA() in your original formula (column B) and use =IFERROR(B2,"") in the duplicate.
Ideally one should be able to use the AGGREGATE(1,7...) in a pivot. Googling if that is possible, I did get a few hits using PowerPivot / PowerQuery. You can try if you like, but its not my strong suite.
I think I personally prefer the pivot refresh option (#3). If the client is smart enough to refresh the pivot table they can surely toggle a cell value. You can even make the "plotforpivot" cell a dropdown list to only allow TRUE or FALSE, and you can include a warning formula in A1 that detects if you have #N/A errors in the pivot and "" blanks in the data e.g.
=HYPERLINK("#plotforpivot",IF(ISNA(GETPIVOTDATA("Data",$D$1)), "WARNING #N/A ERROR: PLEASE TOGGLE PLOTFORPIVOT AND REFRESH THE PIVOT TABLE", IF(COUNTIF(B2:B7,"")>0,"WARNING PLOTTING ERROR: PLEASE TOGGLE PLOTFORPIVOT","ALL GOOD!")))