Plot Hourly Data

[mjwedeking]

How can you plot data that is less than daily? I typically create charts of
data that has hourly or even 15 minute time steps. Excel will sum all the
values for the day and just plot daily data, or monthly, or yearly.

Sample Data:
A B C
Date Time Flow(mgd) Rain(in)
2/13/2007 1:00 1.2 0
2/13/2007 2:00 1.3 0.1
2/13/2007 3:00 1.5 0.2
2/13/2007 4:00 1.1 0

 

[David Biddulph]

Sorry, I don't see your difficulty. Combine date and time in one column, &
use that as the X values for your XY chart.

 

[mjwedeking]

Ok, fine for an XY line graph. What about: plot flow as line and same graph
Rain as bar graph. You will get one big bar for the entire day with the rain
totaled.

 

[Kelly O'Day]

mjwedeking

Two experienced Excel chart advisors have tried to help with the same
answer, use an XY chart, not line chart. I'll try to help them answer your
question by giving you some background on why their advice is what you need.
As a process engineer, I use Excel XY charts to handle data as fine as 1
second.

Point 1: Excel treats time as a fraction of a day. 6 AM is stored as 0.25
(6/24) of whatever day you are using. So 2/13/07 at 6:00 AM is stored as
39126.25 in Excel. Your flow and precipitation data are hourly based. So
when you plot on XY chart, Excel places each hour's data where it belongs on
X axis.

Point 2: Line Charts are intended for category data on X axis. A line chart
with a time scale treats the day or week or month as a category. Line charts
can not handle fractions of a category, they can handle full days, not
fractions of a day. Think of line charts as integer based. If your X axis is
integer based, great, a line chart will work.

Point 3: XY charts can handle fractions of a day.

Point 4: To show both flow and precip, I usually use left Y axis for flow
and right Y axis for precip. I show the flow as a line series, and the
precip as a point series with an error bar extending to 0.

Point 5: I have a number of example process data charts on my site that may
help you understand the approach that the three of us are recommending.


Kelly

http://processtrends.com