This is similar to https://techcommunity.microsoft.com/t5/excel/scatter-chart-plots-wrongly-if-blank-cells-exist-in-input-range/m-p/1337190 , 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

​This is similar to https://techcommunity.microsoft.com/t5/excel/scatter-chart-plots-wrongly-if-blank-cells-exist-in-input-range/m-p/1337190 , 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    Read More

​