Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

plotting 2 independent time series in Excel 2007

Posted on 2011-09-19
11
Medium Priority
?
388 Views
Last Modified: 2012-05-12
Hi,

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
0
Comment
Question by:trcamp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36561440
Attached is a modified version of your workbook.  

water-levels-and-rainfall-for-ex.xls
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1400 total points
ID: 36561489
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
0
 

Author Comment

by:trcamp
ID: 36562047
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36562645
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.

-Glenn

0
 

Author Comment

by:trcamp
ID: 36562688
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.
0
 

Author Comment

by:trcamp
ID: 36567262
Glenn,

Any luck on having the graph work for *hourly* rainfall values (instead of the one rainfall value per day)?    Thanks in advance!
0
 

Assisted Solution

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

Thanks!
WL-vs-rain-for-expert-exchange.xls
0
 

Author Comment

by:trcamp
ID: 36573520
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.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36581192
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.

-Glenn

0
 

Author Comment

by:trcamp
ID: 36581281
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.

TC

re-posted here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27322011.html?cid=239#a36581119
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36581406
hey...I'll take 700 points if I can get this to work for you! <vbg>  I'll post the new workbook over there.
-Glenn
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

609 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