Plotting graphs using data that contains empty cells

[jmasanganise]

Dear All

I have a data set containing X and Y variables recorded at 30-minute intervals. The variables alternate between positive and negative values depending on the time of day (see the spreadsheet attached). A plot of Y against X should produce a straight-line graph passing through the origin. However, there were intermittent problems resulting in either X or Y or both not being recorded (see blank cells). Plotting the variables as they appear does not produce the expected linear relationship.

My first question is: How do I use excel to suppress the empty cells and use the data to plot a straight-line graph which shows both the positive and negative quadrants?

My second question is: How do I use excel to separate positive and negative values so that I can plot separately, positive Y against positive X as well as negative Y against negative X, with empty cells suppressed?

Regards

J.M.

[MrShorty]

I entered =ISBLANK() into the adjacent columns, and all cells returned FALSE -- even those cells that looked blank. Those cells must contain empty string "" or some other non-printing character. Much depends on exactly how the data came to be this way. If you have any control over how the data comes into Excel so that those cells will be truly empty, then Excel will ignore them when charting. Otherwise, replace whatever text string is in those cells with NA(). This is a good essay discussing issues with charting and "empty" cells: https://peltiertech.com/mind-the-gap...g-empty-cells/

I am not sure what your trouble would be with separating out positive and negative values. I would tend to add a couple of additional columns that replace the negative values with N/A for the series where you want to see the positive values separately and vice versa for the other situation.

[jmasanganise]

Thanks Forum Guru for the idea. I will try it and see what comes out.

Regards

J.M.