plotting 2 independent time series in Excel 2007


This issue should be solved in two steps.  The first step is simply removing every other row of data from data in tab 1.

The second step is as follows.

I am trying to plot 3 time series on one excel graph.  Two of these are x-y plots that follow the same date-time series and should be plotted as line graphs (with tick marks on Jan 1, Feb 1, Mar 1, etc).  Y axis is water level, X axis is date-time.  These data are in tab 1 (with every other row removed as per step 1).

The third time series are located in tab 2.  Y axis is rainfall, X axis is date-time.  These should be plotted as vertical bars that are in alignment with the date-time X axis as the two x-y plots from tab 1.   However, and here's the catch, the rainfall data are collected and recorded as a different date-time series.  (As you'll see in tab 2, there are very few rows of rainfall data and only one rainfall per day, and only for some days.)

All three plots need to be on the same graph and lined up according to the correct date.  The two X-Y line plots should share a Y axis, and the rainfall should be a secondary Y axis.

Attached is the excel file.  Thanks! water-levels-and-rainfall-for-ex.xls
Who is Participating?
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Sorry...posted file before adding comments.

You can't plot bar charts and X-Y charts simultaneously, so to work around that I added a third sheet which has a continuous date range and does lookups on the two existing sheets to get the water levels and time-matched rainfall data.  Since you are limiting your plots to one value per day, there is no need to eliminate every other row of data from your "tab 1" worksheet.

The bars are very thin, but they are time-aligned.  The water level data uses a line chart, rather than an x-y chart.  This also allows you to properly label the x-axis on one-month tick marks as you requested.

On a side note, if you did want to remove every other row you could do it this way:
1) insert the following formula in cell C2 of the "tab 1 - WLs" worksheet:  =MOD(ROW(),2)
2) copy the formula to the bottom of the range.  You will see alternating 0's and 1's
3) turn on automatic filtering
4) select either "1" or "0" in that column
5) highlight all the visible rows and then delete them
6) turn off filtering
7) delete the contents of the cells in column C

Glenn RayExcel VBA DeveloperCommented:
Attached is a modified version of your workbook.  

trcampAuthor Commented:
Almost!   I need the "Chart Data" tab to be populated with ALL the water level vs time data (to include 12 hours per day....12 rows per date.....recall that in step 1 we remove every other row from the original dataset of 24 hourly records per day).  The way we have it now I only have one water level per date.  (I need them all because in hope to get my hands on hourly rainfall records (tab 2) and then I will compare water levels to rainfall on an hourly, rather than a daily, basis.)

Please advise.

Thanks much Glenn.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Glenn RayExcel VBA DeveloperCommented:
Before I proceed, just need to clarify something regarding your water level data.

I understand that the water level data is hourly, but if you remove every other report, you will only have 12 reports per day (bi-hourly).  If you get hourly rainfall totals, that data will have to be modified to a bi-hourly rate (i.e., combine every two hourly reports) in order to get data to match up.

Am I correct in this?

I will state that I'm not 100% sure that the chart can be created at a bi-hourly level and still produce a chart as shown.  Additionally, while I can see the advantage of having a table showing this level of detail, the chart will be nearly indistinguishable from what was presented here, especially at this large of a timescale.

I'll keep monitoring and hope to help you resolve it.


trcampAuthor Commented:
Yes, sometimes we are provided hourly totals with zeros in the hours when it did not rain.  At other times, we are provided hourly total with ONLY the hours that it actually rained (only positive values, no zeros).  As long as there is a date-time stamp associated with the rainfall amount, it seems like any set of data - provided to us either way - would give us a correct plot.

In any case, yes, you are correct in your assumptions.

If it takes a bit more programming to get the rainfall dataset in the correct, long hand (WITH zeros in slots when it doesn't rain) format should I submit as a separate question.  I was hoping Excel knew how to plot these, without having to manipulate the original datasets too much.


Thanks Glenn.
trcampAuthor Commented:

Any luck on having the graph work for *hourly* rainfall values (instead of the one rainfall value per day)?    Thanks in advance!
trcampConnect With a Mentor Author Commented:
I think this will help.  

I have attached a spreadsheet with hourly rainfall values in tab 2 - rainfall, and two additional sets of data series in tab 1 - WLs.  Rainfall is to be on a secondary Y axis.  WLs are to be X-Y line plots.  All three plots need to be on one graph, with tic marks at Jan 1, Feb 1, Mar 1, etc.  

The solution provided above worked beautifully (thanks Glenn), but only for daily values, not for hourliy values which I need.

trcampAuthor Commented:
Glenn or others,  Should I "abandon" the question?  

Glenn - I'd like to award you some (most of the) points for the partial answer (the part you did worked great).  If you know how I do that please let me know.  I'll look into it as well.  Thanks.
Glenn RayExcel VBA DeveloperCommented:
Hey trcamp,

Sorry for the delay; I (and my DW) was out with food poisoning for two days.  That's what I get for trying to eat healthy food! Ugh.

Anyway, based on your latest file, one would need to expand Chart Data sheet to include bi-hourly data (which was a bi-product of considering every other data point in the water level sheet).  Then, new formulas would be needed to correctly capture the sum of rainfall totals within the two-hour period (ex. 1/5/2007 2:00 = 0.29) OR only capture the rainfall amount if a single-rain event occurs that doesn't trigger multiple rain gauge readings (ex.1/8/2007 2:00 = 0.18).  It appears that if no rain is recorded over a 24-hour period, the data returns 0 (inches) on the 0:00 of that date.

I'll see if I can produce a modified lookup table for you and post a new workbook here today.  Thanks for your patience.


trcampAuthor Commented:
Wow, sorry to hear about the food poisoning Glenn.  Thanks for getting back.  I didn't know where you went so I gave you 350 points for a partial answer and re-posted the question for an additional 350 points.  If you'd prefer, you could have a look there instead.  Sorry for the mix-up and I really appreciate your help.


re-posted here:
Glenn RayExcel VBA DeveloperCommented:
hey...I'll take 700 points if I can get this to work for you! <vbg>  I'll post the new workbook over there.
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.