Solved

plotting 2 independent time series in Excel 2007

Posted on 2011-09-19
11
336 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
  • 6
  • 5
11 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Attached is a modified version of your workbook.  

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

Accepted Solution

by:
Glenn Ray earned 350 total points
Comment Utility
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
Comment Utility
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
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:trcamp
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

14 Experts available now in Live!

Get 1:1 Help Now