Create Line Chart with multiple lines representing multiple sets of data over time

**GrumpyOldTimer** · 08-26-2019, 06:08 PM

I am trying to understand the rate of flow decline of water filters over time. It appears the filters work fine for an extended period of time, then suddenly decline in flow rate between 3 and 4 months. I need to track, record and graph the performance and fall off of the flow rate over time.
so, I have the dates the flow rate was measured (random dates) for several filters and the exact flow rate,. The flow rate is a single number, the number of seconds to flow 32 ozs. So, for multiple filters I have the dates they were checked, the flow rate for those dates. The number of days is calculated from day 0 (day new) to date of each measurement. The starting dates are not the same for any filter/data set, so days is the key time component representing time in service.
ex: Filter 1 0 days(new) = 21.05 sec.
45 days = 28.35 sec
60 days = 29.56 sec
105 days = 30.39 sec
119 days = 58.51 sec

Filter 2 0 days(new)= 23.82 sec
82 days = 27.53 sec
105 days = 29.65 sec
etc.

What I need to display is a line graph with this data (and several more sets of data) with the elapsed time across the horizontal axis and the flow rate (seconds) on the vertical axis. Purpose is to demonstrate visually the rapid fall off of efficiency of flow rates in the 100 to 120 day range. I would like to have one line for each filter. And I need to maintain the total elapsed number of days to show the sudden and dramatic drop off in the last 30 days.
So, any help you can give on how to graph this data would be appreciated. I am open to the structure of the data as I haven't entered much data at this point. I want to get it right before adding a ton more data.
Thanks in advance for any help you can provide.
Grumpy

**MrShorty** · 08-26-2019, 06:16 PM

Here's how I would tend to do something like this:

1) For each filter, enter "time/days in service" and "flow rate/seconds per quart" in two columns:

Please Login or Register  to view this content.

Repeat for each filter that you have data for.

2) Build scatter (not line) chart for data.
2a) Select data for filter 1 -> Insert chart -> XY scatter -> lines with or without markers as desired.
2b) Fix up anything that you want to fix up.
2c) Bring up the select data dialog and add new data series (both xvalues and yvalues) for each filter that you want to show on the chart.

Evaluate the charts and see if you like them.

Does that help?

**GrumpyOldTimer** · 08-27-2019, 12:58 PM

MrShorty
Thanks much. This worked gr8. I was nearly there, but couldn't get the last step to work. After rereading 2c and following the logic of it I was able to get the correct number of series in the correct order etc. I have not used this feature (adding data) since Excel 2007, not sure if something changed or I just had a mind blank. Anyway, thanks again for the help.
Grumpy
PS, what is the reason for a scatter vs line chart in this case?

**MrShorty** · 08-27-2019, 01:28 PM

Two reasons. "Days" has a numeric meaning. In the first example, 0 to 45 is a longer time than 45 to 60. Compare that to the first two entries of Filter 2 (0 to 82 days) which is even longer than the first three entries for filter 1. Your chart needs to show the numeric meaning of the days value and not treat it as a text category -- which is what a line chart would do by default.

Your examples show that each filter will have different values for days. I find that scatter charts are typically easier to work with when every data series will have its own x and y values. Line charts will struggle with this, so a line chart will really want everything combined against one set of x values, which means compiling all of your disparate x values into a single column. It will still want to treat those as text categories.

**GrumpyOldTimer** · 08-27-2019, 02:40 PM

MrShorty
Thanks much for the clarification. I learned something else that will help me in future Excel work.
Thanks and have a great day. ;-)

Create Line Chart with multiple lines representing multiple sets of data over time

LinkBack

Thread Tools

Rate This Thread

Display

Create Line Chart with multiple lines representing multiple sets of data over time

Re: Create Line Chart with multiple lines representing multiple sets of data over time

Re: Create Line Chart with multiple lines representing multiple sets of data over time

Re: Create Line Chart with multiple lines representing multiple sets of data over time

Re: Create Line Chart with multiple lines representing multiple sets of data over time

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Unique Sumifs - 2 x Data sets, with references but repeated across multiple lines.

Can't figure out how to create 24 hour time series chart with multiple data sets

Multiple Data-sets To One Line

Create Multiple Straight line chart with data being unique data time marks on each line

[SOLVED] Plotting multiple lines in Line Chart that have a very broad scale

Line Graph: multiple lines from multiple data sets?

One-line graph, multiple data sets

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions