Remove Specific Drop Line on a Line Graph

[dinonuggets01]

Does anyone know how to remove a specific portion of a drop line on a trend line? For instance, this is my trend line:

------------
|||||||||||||||\ <--- I want this removed
|||||||||||||||| -----------------

So my trend line is this:

------------

|||||||||||||||| -----------------

My current method is to split the source data into 2 different trend lines. However, I want to achieve the effect using one trend line, so that I don't have to create additional trend lines every time I want to remove a drop line.

[jeffreybrown]

Hard to say without a sample file to work from.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need.

Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dinonuggets01]

Hello, I have uploaded an example work book as you requested.

[jeffreybrown]

I'm not sure I see a way to do this, but I can reach out to our community.

[dinonuggets01]

I'd really appreciate it, thank you!

[CK76]

Only way I know how, is to add blank data.

But this will add another value to x series. Which may not be desired.

As well, in Excel, x-y scatter can't use categorical (non numeric) axis.


See attached that simulate using line chart (notice the gap marker).

[dinonuggets01]

Only way I know how, is to add blank data.

But this will add another value to x series. Which may not be desired.

As well, in Excel, x-y scatter can't use categorical (non numeric) axis.


See attached that simulate using line chart (notice the gap marker).

I have considered the use of blank data, but did not like the added value. I guess as it stands, I have to pick between the use of blank data or add additional source data.
Still, if there is another option I'd love to learn it. In the meantime, thank you both for the help!

[MrShorty]

I kind of liked the approach you used in the "After" sheet -- have each "step" in a separate column/as a separate data series. I expect you want to keep the original data arrangement in the "Before" sheet, so the hardest part of this approach would be to figure out the formulas in the helper columns (can be hidden if you don't want to see them) that will detect each "step" and populate the helper columns appropriately. I haven't thought through the details, because I am not sure you are open to the idea, but it might be workable. Basically, it is very similar to the idea for "conditional formatting" of charts like this technique: https://peltiertech.com/conditional-...-excel-charts/

Where you have a scatter chart, I could also see doing something based on "dynamic named ranges" and "dynamic charts". I'm not sure if you are familiar with these, but they are typically based on the OFFSET() function. See tutorial here: https://chandoo.org/wp/dynamic-chart-data-series/

No details, yet, because I am not sure exactly what variations on the theme you need to account for, what kind of approaches you are open to, and such.

[dinonuggets01]

From looking at the conditional formatting technique, how messy will it be for graphs involving large data sets?
I am not familiar with dynamic charts, but it seems like a better option than conditional formatting. Still, looking at the tutorial you've given, won't dynamic charts also run into the issue of needing a blank space to achieve the desired effect of no drop lines?

[MrShorty]

From looking at the conditional formatting technique, how messy will it be for graphs involving large data sets?

It probably depends on how large a large data set is to you and what it takes for a spreadsheet/chart to be "messy". As I tend to define these concepts, I would expect to exceed the limits of my 2007 charting engine (255 individual data series/"steps" and a quarter million individual points. your version's limits are much higher) before I would consider it "large" or "messy".

won't dynamic charts also run into the issue of needing a blank space to achieve the desired effect of no drop lines?

I see no reason to need a blank space/row between steps. Dynamic chart tutorials like the one I linked to assume that the starting row for the named range is constant and that the only thing it needs to dynamically determine is the number of rows to include. In your case, each named range will need to dynamically identify both the starting row and height of the range, but the OFFSET() function has arguments for both of those. It will be a little bit more complex than typical dynamic charting, but the same basic ideas are still there.

Are you still wanting to go with the dynamic named ranges/chart approach?

[dinonuggets01]

I think I will need to do more learning on my own time in regards to dynamic charting.

With your help, yes I will like to go with the dynamic named ranges/chart approach.

[MrShorty]

Because this will really only work for a scatter chart, we need some numbers we can use for the xvalues for each data series. I would add a column of counting numbers (or whatever values will best meet your needs). (Enter 1 in F2 and =F2+1 in F3 and copy F3 down as far as needed). Or do you want something else here?

The OFFSET() function is going to need a way to locate the starting row for each data series (thinking a MATCH() function here) and a way to count how many rows will be included in each data series (thinking a COUNTIFS() function). The next steps are to figure out to perform the lookup and the count so that the dynamic named range can be identified. My thought is to add another column that will "count" the steps. Something like enter 1 in G2 and =IF(D3=D2,G2,G2+1) into G3 and copy down as far as needed is what comes to my mind. This will create a column of integers where each integer marks the rows for each data series. A MATCH(number,$G$2:$G$2000,0) will return the starting row number for a given series. A COUNTIFS($G$2:$G$2000,number) will tell how many rows to include in that data series. The named range for the xvalues (from column F) for each data series is then something like =OFFSET($F$1,MATCH(...),0,COUNTIFS(...),1) and the yvalues (from columns D or E) is =OFFSET($D$1,MATCH(...),0,COUNTIFS(...),1).

You will need to go through the tedious process of creating a named xvalues range, alertvalues range, and actionvalues range for each step that needs to show up in your chart. In your sample chart, that means 6 named ranges to cover the 4 visible steps, or 9 named ranges if you want to be able to add data to blank rows 2:6 (assuming there would only be one step in these). For each additional "step" grouping that you add to the data, you will need to define 3 new named ranges. Setting it up will be a bit tedious -- especially if you have a large data set with several steps. But, once the dynamic named ranges are all defined, you should not have to do it again (unless you need to add more when you encounter a larger data set).

Is that easier than adding blank rows like CK76 suggested? I don't know if it is. But it will work if you are willing to define all of the named ranges that you will need.

[MrShorty]

I would also note that, because of the tedium of creating a bunch of dynamic named ranges, I thought it would be easier to create helper columns and use something more like the "conditional formatting" approach, if you are interested.

Using the same G column, I added a row across the top of the sheet with 1, 2, 3, (say in J1, K1, L1). then in J2, a simple IF() formula like =IF($G2=J$1,$D2,NA()) (copy and paste into column J:L) will pick out the appropriate steps for the alert values. Repeat that process in N:P to get the steps for the action values. Then build the chart using these helper columns.

I found this a lot faster and less tedious than defining all those named ranges. You would need to know how many columns/steps to plan for so that you have enough helper columns to cover every scenario you expect to see. You might reconsider if this will be easier.

[dinonuggets01]

Thank you so much for your explanation. I have to work on a different project at the moment, but will explore and try to implement your solution when I get the chance. I will come back and let you know my progress when I do.

Thank you again!