• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Connecting points on a chart instead of returning to zero

D

DashboardNovice

Member
On the yellow tab, I have a data series called 15-min displayed (R75 is checked) on the 2nd graph. I am trying to get a continuous line to connect all data points. When Excel encounters a blank in column R, it appears to graph that as a zero.

I'm sure the answer is in the window below, but I can't figure out which combinations should be selected.

Hide Empty Cells.jpg


Also, on the 2nd graph, the 30 minute chart and possibly others appear to display on the zero line. How can I get it to not show any color plotted at zero since I'm not plotting anything for those data series anyway (the boxes on row 75 are not selected for those data series).
 

Attachments

  • SPX.xlsx
    323.1 KB · Views: 3
DashboardNovice said:
On the yellow tab, I have a data series called 15-min displayed (R75 is checked) on the 2nd graph. I am trying to get a continuous line to connect all data points. When Excel encounters a blank in column R, it appears to graph that as a zero.

I'm sure the answer is in the window below, but I can't figure out which combinations should be selected.

View attachment 89967


Also, on the 2nd graph, the 30 minute chart and possibly others appear to display on the zero line. How can I get it to not show any color plotted at zero since I'm not plotting anything for those data series anyway (the boxes on row 75 are not selected for those data series).
Click to expand...

When I watch videos on YouTube they select the option "Connect data points with line" and it works, but when I try it, for some reason it's not working.
 
It's because your cells are not actually empty - they contain formulas returning "", which is treated as 0. As it happens, if you removed the IFNA from your VLOOKUPS so that they return the #N/A errors, it would fix the problem since line charts ignore that error. You could then use conditional formatting to hide those errors in the columns if required.
 
Debaser said:
It's because your cells are not actually empty - they contain formulas returning "", which is treated as 0. As it happens, if you removed the IFNA from your VLOOKUPS so that they return the #N/A errors, it would fix the problem since line charts ignore that error. You could then use conditional formatting to hide those errors in the columns if required.
Click to expand...

Last night I thought about that, tried it and it worked. Thank you for explaining why this occurred.
 
You must log in or register to reply here.
Back
Top