Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

need Excel plot showing 3 sets of date-time data series

Posted on 2011-09-27
8
Medium Priority
?
282 Views
Last Modified: 2012-05-12
Hi,

This should be a pretty straightforward solution for one who is skilled in Excel plotting, but I've written a few paragraphs to make the problem and goal very clear.   Basically, I am trying to create a graph that shows 3 sets of data....details follow:

I have attached an excel spreadsheet with 3 tabbed worksheets, with each tab corresponding to data collected in a different town.  Each tab contains: column A = date-time of hourly rainfall, column B = hourly rainfall, column C = blank, column D = date-time of hourly water level in  shallow well, column E = hourly water level in shallow well, column F = blank, column G = date-time of hourly water level in deep well, column H = hourly water level in deep well.  

Goal:  To create one graph (showing all three data series described above) for each town's tabbed worksheet...three graphs in all, one per town.     The graph will show all three sets of time series data discussed above.  The rainfall data series (columns A and B) should be vertical bars on a secondary y axis.   The shallow well water levels should be an x-y line plot (columns D and E).  The deep well water levels should be an x-y line plot (columns G and H).  Each graph should have tic marks at monthly intervals at Jan 1st, Feb 1st, Mar 1st, etc. and span the time that we have recorded rainfall data available.  

One final note:  There are a lot of rows of data.  If "processing speed" becomes an issue, feel free to remove every Xth row of water level data (columns D and E and columns G and H) to end up with only 6 hourly values of water levels per day instead of 12 or 24.  However, ALL rainfall rows (columns A and B) must remain for a total of 24 values per day, so please make sure rainfall rows are not removed.  

Since only "partial solutions" have been provided for this question in the previous two tries, only a complete solution will be awarded points this time.  Thank you for understanding, and thanks in advance!

WL-vs-rain-for-expert-exchange.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
  • 4
  • 2
  • 2
8 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 36711481
See if this is what you want

Kyle
Q-27344012-RevA.xls
0
 

Author Comment

by:trcamp
ID: 36711638
No.  The data points do not seem to correctly match up with the correct date on the horizontal (date) axis.  This includes the rainfall data (vertical bars) which seem to be way off.    Did you check this and were they correct on your machine?
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36711863
Well, isn't that fantastic.  Change the chart type for the rainfall to a column and it ignores the dates.  hmmm.....I'm going to have to think dig a little bit.  Sorry for the delay.

Kyle
0
Industry Leaders: 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 81

Expert Comment

by:byundt
ID: 36712084
I plotted the data as line chart with time axis using VLOOKUP formulas to bring over the shallow and deep levels. Custom scaling on the primary and secondary y axes put the rainfall data above the level data for less confusion.
WL-vs-rain-for-expert-exchangeQ2.xlsx
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 700 total points
ID: 36712187
trcamp,
Take a look at this.  I'm not sure why it wasn't working the first time.  Seems really glitchy, like it would work and then stop working and then work again.  I dunno.

Brad,
Have you had trouble with Excel messing up the horizontal axes when doing plots on two axes with multiple plot types?

Kyle
Q-27344012-RevB.xls
0
 
LVL 81

Accepted Solution

by:
byundt earned 1300 total points
ID: 36712256
For many chart types, Excel assumes that the X-axis is arbitrary text with no scaling applied. That can mess you up if you aren't using a scatter chart (or a line chart with time axis). That's why I did the VLOOKUP to bring over the well level data into the rainfall table--each of the three series then has the same number of data points (though many of them are NA() function so they don't plot).
0
 

Author Closing Comment

by:trcamp
ID: 36712347
Excellent, well done!   Thanks very much to you both - I appreciate your help!
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36712383
Thanks for the assist:)

BTW, Brad, nice use of vlookup.  I always set the 4th parameter to false and forget about it.  Very good!

Kyle
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 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