2

So in a moment of bizarre nostalgia I made a basic turtle graphics (aka LOGO) interpreter in Excel:

enter image description here

I am trying not to use VBA. Any idea how to make a "blank" line on the scatter plot to simulate a pen up command? If I delete the formula completely it works, but that's not practical. "" makes a line through the origin and NA() just "pulls" the line to the next point.

Improve this question
2
  • In Excel if Formula returns #NA then graph does't considers that while creating Chart even though Hidden & Empty option's GAP/ZERO/CONNECT DATA POINT is been applied,,, if literally #NA is entered the Excel considers it as ZERO while create the Chart !!
    – Rajesh Sinha
    Commented May 15, 2021 at 5:06
  • In my experience with Office 2016 and earlier, text and ="" is treated as a zero in a scatter plot, =NA() returns the actaul error and will span the line to the next point (unless in Excel 365 you set the "treat N/A as empty" setting). It seems only a truely empty row (no value or formula) makes a break in the plot line in older Excel versions.
    – ExcelEverything
    Commented May 15, 2021 at 9:16

2 Answers 2

Reset to default
1

For scatter plot you can change how empty cells are managed.

See more details here:

https://support.microsoft.com/en-us/topic/display-empty-cells-null-n-a-values-and-hidden-worksheet-data-in-a-chart-a1ee6f0c-192f-4248-abeb-9ca49cb92274

Improve this answer
4
  • Sadly I don't have Excel 365 in order to have #N/A errors be treated as blanks. But it should work in new additions.
    – ExcelEverything
    Commented May 15, 2021 at 0:42
  • It not a new feature in excel 365. The link says it's available since excel 2007.
    – Máté Juhász
    Commented May 15, 2021 at 4:33
  • If you scroll down you'll see the note that says the specific option to treat NA as empty cell is only in Excel 365
    – ExcelEverything
    Commented May 15, 2021 at 9:09
  • The only solution I can think of for earlier Excel versions are VBA UDF's that plays dirty. Say two volatile UDFs copyto() and delete() that can be used like this in say C3 '=IF(PenUp=TRUE, delete(D3), copyto(D2,D3))`. Or perhaps VBA code in a worksheet change event that edits the individual points on the graph and hides the lines that are tagged pen up. In theory this could be useful to also change colours of line segments.
    – ExcelEverything
    Commented May 15, 2021 at 9:29
1

I have done this before successfully. There are 2 key things you need to do. First, don't use a trendline. Instead, create a scatterplot then right-click, change chart type, and choose "scatter with straight lines".

enter image description here

Second, if you want to have a "pen up" the line needs to have #n/a for either x or y (or both). =na() should work equally well.

enter image description here enter image description here

Improve this answer
2
  • This doesn't work for me on Excel 2016, but it would help in combination with the solution proposed by @Mate which requires you to change the "hidden and empty cells" settings in "Select Data Source" dialogue. With the correct setting #N/A will show blank and without a line.
    – ExcelEverything
    Commented May 15, 2021 at 0:39
  • Yay for teamwork!
    – Greg Viers
    Commented May 15, 2021 at 1:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .