Mar 31 2022 12:45 AM - edited Mar 31 2022 01:40 AM
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?
Mar 31 2022 01:57 AM
@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.
Mar 31 2022 02:14 AM
Mar 31 2022 03:05 AM
@pfridolf Try using NA() in stead of "" and make sure that you the axis labels in a range that contains number starting with zero.
Mar 31 2022 03:11 AM
@pfridolf Forget about the NA() part. You don't need it. Have look at the attached file and try to replicate to your own.
Mar 31 2022 04:23 AM
@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.
Mar 31 2022 04:44 AM
Solution@Per Fridolfsson OK! Played around a bit. Try the attached file, using NA() in the column for the X-axis.
Mar 31 2022 04:56 AM
Mar 31 2022 04:44 AM
Solution@Per Fridolfsson OK! Played around a bit. Try the attached file, using NA() in the column for the X-axis.