i need to plot three different sets of time-series data on one graph

Hi,

I need to create one graph for each of the three tabs in the attached spreadsheet.  (Each tab contains water level and rainfall data obtained at different sets of wells.)

Each graph will depict three sets of time series data.  One set will be vertical bars on a secondary y axis (from columns A (date) and B (rainfall).  The second will be an x-y line plot from columns D (date) and E (water levels at well a).  The third set will be an x-y line plot from columns G (date) and H (water levels at well b).  Each graph should have tic marks at Jan 1, Feb 1, Mar 1, etc.

Thanks!

TC WL-vs-rain-for-expert-exchange.xls
Who is Participating?

Author Commented:
It looks like everyone has given up.  I will need to close question shortly.

Thanks to each of you who has tried... I will try to be fair about point distribution based on the results provided.  Again, thanks to all.
0

Finance AnalystCommented:
I have thought of a way of doing this but coming up with some issues.

I have added a tag to the rows of data for Rainfall (R) and Well 1D (1D) and Well 1S (1S).

I have then copied the 3 blocks of data including the tags into one block so you have date, data and tag as 3 column headers. This can then be sorted by date, giving one sorted block. Alongside this you can then create 3 additional columns of data pulling out the data from the data column into a column depending on tag. So you now have one table with 6 columns of data with Date, All Data, Tag, R Data, 1D Data, 1S Data.

This contiguous block of data can now be used for a graph, with 1D and 1S as line graphs on a secondary axis.

However, and this is the so far unresolved problem, because the data often goes to zero, the line does zigzags up and down to the x axis giving what looks like a block rather than a line.

Thanks
Rob H
0

Excel VBA DeveloperCommented:
Hey trcamp...almost have this figured out, but am running into an issue with your water level data.

The original data set was significantly lower (ranging 8-16) than now (3600-3800).  Additionally the differences between the two water levels (avg. 175 units) is so large that if you plot them both on a chart, you cannot really see the changes in the water level.

Can these two sets of values be normalized (as I believe they were in the first instance)?  If so, just provide more detail about what is being measured here.
0

Author Commented:
These are water level elevations, relative to sea level.  On tabs 1 and 2, the x-y data plot close enough together, as long as rainfall is on the secondary axis.  On tab 3, the water level elevations are quite different between the two wells, which makes it hard to see any differences when plotted at a scale to see them both.  I don't know a workaround unless Excel offered a third (teriary) Y axis.  (I'll have to figure out some workaround on my own I guess.)

Does this help?
0

Excel VBA DeveloperCommented:
If you must see the two water levels simultaneously - and be able to view any significant changes to them - they will have to be normalized in some way.  Usually this is just a phase shift of the values of one relative to the other so that both sets of values fall in the same general range.

In your case, the values for well 4D average about 125 points below that of 4L.  To normalize, one could determine a "norm" value for each well and then plot the difference of the water level against that norm.  The chart would then be titled as such (ex. "Deviation from well norm").  An appropriate footnote would be recommended to explain.

0

Author Commented:
Yes, that would work.
0

Commented:
I would normalise as suggested by GlennLRay: and use look-up as amount of data an periods do not match.

See what you think of attached graph for Site 4:

WL-vs-rain-for-expert-exchange-C.xlsx
0

Author Commented:
Thanks cben.  The dates for rainfall do not seem to match the x axis.  Also, if I normalized I would want to do it in a way in which both x-y graph line variations are readable.  And I do not really need the moving average rainfall.  Although this solution does not work, I appreciate your input.

Also, I don't want to shift the focus away from the plot problem itself, as outlined in my opening description.  (Getting an accurate plot for the data contained in each tab is more important than the normalizing issue for the data in tab 3.)

This question is still open.
0

Excel VBA DeveloperCommented:
Hi trcamp,

Okay, one of the big problems arising from all the new data is the the time required for Excel to process large quantities of data for this chart.  So, I preserved your original data, but am only showing a subset of results in the chart as an example.

Before uploading the workbook, let me know if the following shows how you would like the graph to appear:

This is from data in your original question (which I assume is unchanged in the file attached in this question).  I normalized the water levels for 4L and 4D against their means across all reported values (4L-3797, 4D-3628).

Normally, a metric like rainfall would not have a negative component, but its scale was made so in order that it would be proportional to the scale needed for the water level variance.

Again, this is only a subset.  Trying to produce this chart for approximate 35000 data points for only two wells is VERY processing intensive on Excel's part (about 4 mins to generate after any change or activation of the chart).

-Glenn
0

Author Commented:
Yes, this looks great.  Not sure why the negatives appear but otherwise it's what I'm looking for.  Also, I was thinking there were a few rainfalls above an inch(?)

Anyway, this is what I'm looking for.  I can remove more values per day - say use every 4th value - (WLs only.....all rainfall rows need to remain) to get the total number of rows down.

TC
0

Excel VBA DeveloperCommented:
RE: rainfall totals...I will check my formulas to ensure that 2-hour cumulative totals are being shown (I might have missed that)

RE: negative values.  This just means that the water level reported happens to be BELOW the norm chosen.  I will provide a couple of cells (one for each well) that will allow you to choose your own "norm" which would allow you to phase shift each into positive territory, if so desired.  I should probably have the chart display that value automatically (easily done).

I have to leave work early due to a personal matter but will follow up (with file) as soon as I can.
0

Commented:
Hi

Take your point about up to one day mis-match in dates. Now corrected.

Cutting down data a good idea but remember that the transit time from rainfall to borehole level varies, can be many month. (I am water engineering consultant).

Your rainfalls are one hours intervals for site 4 and there are data points missing therefore you need to pick up a water level for each rainfall data point. As water level does not change quickly this should not be problem.

Only one > 1inch rainfall value in the time you have level data.

Here is an option taking these and your points into account. You can knock out rows you don't want.

Hope this looks better. Can be extended to other sites quickly.

WL-vs-rain-for-expert-exchange-C.xlsx
0

Author Commented:
Thank you cben.  However, rainfall still does not seem to be plotting correctly on the x axis.  Also, the only data I need to actually show are the data where we have all three data series (we don't need to plot water levels for periods when we have no rainfall data.

Please note:  ALL rainfall values must be plotted.  The only rows that can be removed (if needed for purposes of speeding up the computation and proccessing speed in Excel, are rows from the water levels.  If any water level rows ARE removed, please make sure that at least 6 are left for every 24 hour period (in other words, we need to have at least one water level reading every 4 hours....and we need one rainfall reading every hour).

Please also note that the three data series (rainfall, water levels in shallow well i, and water levels in deep well D) are all independent data.  They simply need to be plotted on the same graph.  In other words, we do not need to try to "match" plotting a water level for a certain rainfall point, as suggested by cben above.

Question still open.
0

Author Commented:
Increasing the points to 400 to try to get this one wrapped up.  Thanks everyone.
0

Author Commented:
Still seeking solution.....Please read original post for a clear description of what I need.
0

Commented:
Think  we are confused as your request and the other comments ambiguous. For example "ALL rainfall values must be plotted" then points can removed.

Could be that you should start with purpose, then perhaps someone can fulfil that.
0

Author Commented:
Sorry for any confusion cben.

0

Author Commented:
Thanks all.
0

Author Commented:
GlennLRay was only supposed to get 100 points here.  Not sure where the 225 came from....?

Thanks.
0

Commented:
OK - hate to not complete this project.

With a bit of forcing and dropping data where there is no WL to go with rainfall or visa versa I got this.

Hope it helps
WL-vs-rain-CBB3.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.