Scatterplot with empty cells

Question

Hi Community,

I have been experiencing some difficulties in trying to come up with a scatterplot from a set of data having empty cells. The data is much bigger than what I am about to show. I just decided to copy a small part to illustrate here my case.

I also would like to clarify that deleting rows won't be an option because in the real case i am building my graphs from data filtered from a database using pivot tables. Therefore, I really would need to be able to do a good scatterplot considering the empty cells.

Without much more introduction. Here below a small illustration of the situation:

When I create a scatterplot from the previous table I get :

... and you can quickly realize that there is something wrong. It is kind of putting in the X-axis the row number instead of my real X-axis according to the table and there are also some weird points in the Y-axis.

what I am looking to obtain is this result:

which is the result I would obtain If I delete incomplete lines, but yet again, that won't be an option for me.

Do you know if this is possible ?

Thanks for any help.

Julian

Answer 1

Hello,

Thank you for your reply. Unfortunately, I cannot provide acces to this dataset. However, I found a workaround:

Instead of using IFERROR(B2,""), I used IFERROR(B2,#N/A). In this way, when doing the scatter plot, it takes into account only the rows where I have both X and Y (like in the image below).

Regards,

Julian

Answer 2

Could youprovide a link to a workbook with just this problem in it on some file sharing site?

Answer 3

I think I have identify where the problem comes from. In reality I have a formula in my columns:

and this column E despite is "empty", is not really empty. I created another columns to verify with ISBLANK. and it shows me the "empty" cells are not actually empty

This is what seems to be creating the problems when I create the scatterplot.

Do you know how to force and empty cell with my formula IFERROR ?

Thanks.

Answer 4

Thank you for your answer,

When I write by hand the numbers from the table above, I get the same result as you, but when I copy the data from the table even though I pasted without format (only values) for a strange reason I get the behavior I described in my original message (it's really frustrating).

Just in case I'm in windows.

Answer 5

This is what I get when I select the data and choose as shown:

which results in:

This is what shows when I select data and edit series (NO EDITING WAS DONE or NEEDED):

All I then needed to do was to adjust the max/min for each axis to get:

The data on the sheet was not touched/edited at all.