Plot the difference between two values over time

[jackjstokes]

Hi,

We have various machines with screws and barrels. Over time, the screws wear against the barrels, so the screws get smaller and the barrels get bigger and so the gap between them gets bigger.

I've attached a log of one of the machines, where I've input the data at the date the measurement was taken, then subtracted one from the other to give the gap between the screw and barrel.

I want a nice visual way of displaying on a graph this increasing graph trend. How is the best way to do it? I can't get my head around it.

Thanks in advance.

[MrShorty]

It's not clear to me how you want to show the data. Going for the low hanging fruit, I:

1) Selelct B2:D2 ctrl B5:D5 ctrl B8:D8 and so on so that I have a multi-range selection that includes just the "gap" rows.
2) Insert -> chart -> line chart

I end up with a line chart with dates on the horizontal axis and gap on the vertical axis, and multiple lines/data series for each zone. As a first guess, what do you think?

[jackjstokes]

It's not clear to me how you want to show the data. Going for the low hanging fruit, I:

1) Selelct B2:D2 ctrl B5:D5 ctrl B8:D8 and so on so that I have a multi-range selection that includes just the "gap" rows.
2) Insert -> chart -> line chart

I end up with a line chart with dates on the horizontal axis and gap on the vertical axis, and multiple lines/data series for each zone. As a first guess, what do you think?

Thanks for the reply, I was hoping to have the screw and barrel logged on the same graph, with the gap shown between them, shaded in. That way, I can see visually where the cause of the gap is coming from, screw or barrel

[MrShorty]

That sounds like you want a stacked area chart. I expect it will be very hard to read more than one screw/barrel pair on a chart (unless you intend to use filters to display only one pair at a time).

1) Select the date and screw and gap rows for one pair. For zone 2 left, for example, select B2:D2 ctrl B6:D6 ctrl B8:D8.
2) Insert a stacked area chart. If Excel opts to "plot data series by columns", execute "switch row/column".
3) Format the chart elements as desired (Axis minimum set to 130 or similar if Excel defaults to 0. Format the "screw" data series to have no fill color in order to highlight the gap series, and any other formatting you want).

If that does what you want for a single screw/barrel pair, then you can repeat the process for each pair and have a chart for each pair. Or we can use lookup or filter tools to extract the data for a single pair into a helper range and use that helper range for the chart. It all depends on how you intend to interact with this data in the spreadsheet.