Thread: Time Graph

I have tried several graphs and cannot find one that will show me what I need. I have attached a spreadsheet to help. I have a list of various times throughout the day. I need a graph that will show the points on a scale from 7:00 AM to 7:00 PM. Or like: 7:00 AM - 10:00 AM, 10:00 AM - 1:00 PM, 1:00 PM - 4:00PM, 4:00 PM - 7:00PM.

Please Help! Thanks!

I'm not sure if you're looking to map these data points by grouping the hours by 3 and then you have some value you want to chart or if you want the # of occurences within each time range or if you want to separate by day.

Excel's basic charting/graphing functions do minimal data manipulation. You need to manipulate the numbers before you chart. Pivot tables do some manipulation.

I tried using grouping in Pivot tables but (at least in Excel 2000) you can't group in sets of 3 hours, just single hours (see attached). Then I set up a table and using SUMPRODUCT, manipulated the data that way. For # of occurrences within each range

Code:

=SUMPRODUCT(--($A$2:$A$35>=D2)*--($A$2:$A$35<=E2))

For Average data values

Code:

=SUMPRODUCT(--($A$2:$A$35>=D2)*--($A$2:$A$35<=E2)*$B$2:$B$35)/SUMPRODUCT(--($A$2:$A$35>=D2)*--($A$2:$A$35<=E2))

Then I graphed that table.

Maybe I can help more after you've clarified my first question.

ChemistB