Solved

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

Posted on 2011-09-22
20
405 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:trcamp
  • 11
  • 4
  • 4
  • +1
20 Comments
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 50 total points
ID: 36581119
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36581511
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 Comment

by:trcamp
ID: 36581650
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 225 total points
ID: 36581798
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 Comment

by:trcamp
ID: 36581828
Yes, that would work.
0
 
LVL 1

Expert Comment

by:cben
ID: 36586157
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 Comment

by:trcamp
ID: 36586616
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36588646
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:
 Water Level & Rainfall graph
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 Comment

by:trcamp
ID: 36588705
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36589747
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Assisted Solution

by:cben
cben earned 150 total points
ID: 36592072
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
 

Assisted Solution

by:trcamp
trcamp earned 0 total points
ID: 36598963
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 Comment

by:trcamp
ID: 36599763
Increasing the points to 400 to try to get this one wrapped up.  Thanks everyone.
0
 

Author Comment

by:trcamp
ID: 36601262
Still seeking solution.....Please read original post for a clear description of what I need.
0
 
LVL 1

Expert Comment

by:cben
ID: 36710640
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 Comment

by:trcamp
ID: 36710662
Sorry for any confusion cben.

0
 

Accepted Solution

by:
trcamp earned 0 total points
ID: 36710697
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
 

Author Closing Comment

by:trcamp
ID: 36898927
Thanks all.
0
 

Author Comment

by:trcamp
ID: 36711108
GlennLRay was only supposed to get 100 points here.  Not sure where the 225 came from....?

Thanks.
0
 
LVL 1

Expert Comment

by:cben
ID: 36719763
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now