Excel graph using formula, not graphing a formula?

[NiteClerk]

Hi, I have Excel 2007
I'm looking to select data for a graph based on a formula. Not graphing a formula. I have Excel 2007.

I have a data set where each row represents a date, then work closed out by hour (6 times a day). The relevant data I use is below:

Row 2 is 4/4/11 , 50 , 75, 20, 110, 40, 34.
Row 3 is 4/5/11 , 25, 40 , 75, 80, 15, 72
Row 4 is 4/6/11 , 130 , 0 , 26 , 44 , 54, 86
Row 5 is Thursday's data
Row 6 is Friday's data
Row 5 is 4/9/11 0, 0, 0, 0, 0, 20   (---Saturday---)
Row 6 is 4/10/11 0, 0, 0, 0, 0, 15   (--Sunday --) (Weekends I just dump into the end of the day column.)

The columns I get graphing data from are actually C, E, G, I, K, M, O and P.

My data is currently 87 rows and grows daily. I'm graphing by taking the 9:00 data and entering =AVERAGE(Data!C2:C6,Data!C9:C13,Data!C16…
The 11:00 run is =AVERAGE(Data!E2:E6,Data!E9:E13,Data!E16…

I do this for 9:00, 11:00, 13:00, 14:30, 17:30 and after 17:30. Also a weekday Average and a Saturday and a Sunday average. (Sat and Sun are just =AVERAGE(Data!O7,Data!O14,Data!O21,Data!… and then Column P.


Plus I make a graph for work orders by day of the week using the technique similar to the above.

This is getting unruly, bulky and more and more error prone.

What I would like is either a Macro where I could select the maximum row number or date and Excel would select the data. OR where I enter a maximum row number or date into a For Next formula and the For/Next does the grunt work.

For the weekends I run the report on Monday and just dump the results into the after 17:30 daily total. Due to the Saturday/Sunday rows I can't just click and drag to select the data.

Any useful advice?

Thanks.

Bob

 

[SkipVought]

Hi,

Your explanation is very hard to follow.

Why do you post only SIX values per row, when you state that you plot EIGHT columns?

What column is on the row, that identifies the DATE for the row's data?

Does your table have headings in row 1?

Please post actual rows of data, with headers of you have, for the EIGHT columns you are plotting.

Please use [blue]Process TGML[/blue] tags to space the data in proper columns. If you do not know what that is, SEARCH in this page for [blue]Process TGML[/blue] and use the TT /TT tag pair.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Also you post PART of a formula...
[tt]
=AVERAGE(Data!C2:C6,Data!C9:C13,Data!C16...
[/tt]
DOT DOT DOT???

Is this formula REALLY relevant to the question at hand? If so, please explain the relevance in detail.

An observation, regarding your formula: it is not well written, as it seems to have multiple HAND HACKED ranges. This may be due to a bad workbook design or a bad formula design or both. But that's an entirely different issue that your original post.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]