Sorry, this is long to explain the issue and my attempts at fixes.
I am trying to make Excel scatter plots of 3-10 separate line segments (un-connected) in top, elevation and side views. The app must allow the user to select the number of segments so the plots must accommodate this. I’m using Excel from Microsoft 365 version 2403 (build 17425.20176).
The values for the end points of the line segments are created using this cell formula:
=IF(NumberOfThrusters>=1,MakeThrusterVector(H14:J14,F14:G14,0.2*MAX(X_Size,Y_Size,Z_Size)),Blanks())
The formula makes two rows defining in 6 columns the point pairs necessary for each view. For now, I’m just working with the plan view plot (first two columns).
There are ten of these formulas each followed by a blank line to accommodate up to ten line segments.
My VBA function “MakeThrusterVector” makes six columns and two lines of point pairs that define the endpoints of the line segments. There is a blank line between each pair of points so that the scatter plot can plot them as unconnected. My VBA function Blanks() produces a variant of six columns and two lines of “” for undefined segments.
The output of the cell formulas looks like this:
I’ve highlighted the first two columns as values for the top view plot; the dark blue are the data pairs for the segments to plot; the lighter blue are the segments that are not defined because the user has selected just 6 segments.
This is the desired plot (however, the selected data does not include any undefined points):
I have a separate series that defines the dotted box so the scatter plot has two series: the box and the segments.
The problem is if I select in both the dark blue and light blue column 1 as x-data and column 2 as y-data for the scatter plot that the undefined points cause the x-values to become the index number of the point rather than the x-value in the table:
The box is at the left, scaled appropriately, but the segments have x-values of the index numbers of the points rather than the x-values in the table.
On my way to dynamic ranges, I have tried to limit the plot data to only the defined points by making tables of only the defined point data:
LimitedXvalues =OFFSET(XYvectors,0,0,NumberOfThrusters*3,1) and
LimitedYvalues =OFFSET(XYvectors,0,1,NumberOfThrusters*3,1). (XYvectors is a named range for the top view segments.) In this case, this should be just 18 rows of x-y points.
(I have even tried this: =IF(ISBLANK(OFFSET(XYvectors,0,0,NumberOfThrusters*3,1)),"",OFFSET(XYvectors,0,0,NumberOfThrusters*3,1) to make sure the blank values are blank and not zero.)
But somehow, the index values again are used in the plot:
This time, the segments are no longer plotted separately and the x-values are still the index numbers. I have verified that the plot should use gaps for blank rows.
Unbelievably, if I simply copy and paste the values for the 6 segments thus use just the numeric values, the plot is the same as above!
Even creating a new blank chart rather than editing the same one over and over shows the same behavior.
I’ve pretty much run out of ideas.