Diagrams with dynamic tables

[pfridolf]

Hi All

I'm sure the answer to this is in the community, but I really can't find it.

When plotting a XY diagram from a table containing "non empty cells", Excel seems to count the number of points instead of plotting the actual X-value. I guess the trick is to empty the cells, but how?

Below is a simple example. The blue table is entered manually with the last rows empty. The other table contains "". Is there any way of getting these rows to be empty or to plot in another way?

[#2]

@pfridolf The only way I can replicate your chart is by shifting the axis labels for the orange line as shown in the picture. Re-align the axis labels (i.e. the reference to column D and the lines will overlap, even when using "" to mark empty strings.

[#3]

Thank's for your reply. Unfortunately the blue table doesn't exist in reality. It was just created to make the example. In reality the orange table is a number of connected tables (including "") and then sorted by the X-values. The "" will then be at the bottom of the table. As the sorted table varies in length, even the empty rows needs to be plotted.
I've also tried to use Trim("") instead of "", that really empties the cell, but unfortunately no difference.

[#4]

@pfridolf Try using NA() in stead of "" and make sure that you the axis labels in a range that contains number starting with zero.

[#5]

@pfridolf Forget about the NA() part. You don't need it. Have look at the attached file and try to replicate to your own.

[#6]

@Riny_van_Eekelen Thanks for your answer. Unfortunately my tables comes from IF statements with a final sort-function. In the IF statements I have to specify "something" when the condition is not fullfilled. In my case "". As soon as this is the case for the X-values, the diagram won't be correct.

In your Exelfile I added an extra table to underline my problem. (It seems that I'm not allowed to attach files). In cell A19: =IF(ISNUMBER(A2);A2;"") In cell B19: =IF(ISNUMBER(B2);B2;""). (Row 19 to 33).  The plot will then be incorrect. 

[#7 · Accepted solution]

@Per Fridolfsson OK! Played around a bit. Try the attached file, using NA() in the column for the X-axis.

[#8]

Seems like the NA() does the trick. Thank's a lot.