Your question, your audience. Choose who sees your identity—and your question—with question security.

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

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

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

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.

Does this help?

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.

See what you think of attached graph for Site 4:

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

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.

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

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

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.

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

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.

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

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

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.

All Courses

From novice to tech pro — start learning today.

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.