Excel XY scatter plots are inaccurate

[unclejohnjohn]

This is similar to https://techcommunity.microsoft.com/t5/excel/scatter-chart-plots-wrongly-if-blank-cells-exist-in-inp... , except plotting my data with blank cells works fine.

I have a sheet where I have cleaned up the data to select points that meet certain criteria. I have a formula with two IFs. If the x and y values meet the criteria, the Y value is copied into the current cell. If not, it gets "". This looks good, I can take the average and standard deviation and get reasonable values, as if the "empty" cells were truly empty.

When I plot these selected data, things get strange. If I plot X and the selected Y values, it looks OK. If I add another Y series, it may be plotted wrong. In one case, the datapoints should show a negative slope, but the plot has a positive slope. If I plot the selected data second, it may be somewhat shifted to the left.

I think this used to work, but maybe I am recalling sheets with truly blank cells. I put a sheet like that in the attached workbook, and it is fine.

Is there a way to clear this problem such as using another character or NaN symbol? I don't want to use zeros as I want the average, etc. to be correct.

Thanks,

John

[#2]

@unclejohnjohn 

Not sure that I fully understand what you are doing, but when I look at the data selected for the orange dots ("test") I see that the header is a single cell (the red box) with all the data below it (the blue box).

However, the data for "val sub T1" is set-up differently. The header spans all the way down to sequence 663 and the data starts at sequence 664. That's causing the shift in the chart.

Fix that and both plots will overlap. The fact that "val sub T1" contains blank cells and "test" contains a formula referring to a blank cell makes no difference.