Obtaining Peak Average High

[Biff]

Here's an alternative method:

Assume the following:

20 wks of data = 140 dated entries = 140 rows of data.
You want 5 weeks of data averaged so there are 4 groups.

Col A = date
col B = weekday
Col C:Z = 24 hourly values
Row 1 A1:Z1 = headers

So, the actual data is in the range A2:Z141

In AA2 enter: =MAX(C2:Z2)
Double click the fill handle to copy the formula down to
AA141.

Now, setup a data table to hold the averages:

In AC2:AC8 enter the weekdays in the same format as you
have them entered in col B - Mon,Tue,Wed or Monday,
Tuesday etc.

In AD1:AG1 enter your headers that define the four
different 5 week periods.

Enter these array formulas, CTRL+SHIFT+ENTER, in the
following cells:

AD2 =AVERAGE(IF($A$2:$A$140>=$A$2,IF
($A$2:$A$140<=$A$2+34,IF($B$2:$B$140=$AC2,$AA$2:$AA$140))))

AE2 =AVERAGE(IF($A$2:$A$140>=$A$2+35,IF
($A$2:$A$140<=$A$2+69,IF($B$2:$B$140=$AC2,$AA$2:$AA$140))))

AF2 =AVERAGE(IF($A$2:$A$140>=$A$2+70,IF
($A$2:$A$140<=$A$2+104,IF
($B$2:$B$140=$AC2,$AA$2:$AA$140))))

AG2 =AVERAGE(IF($A$2:$A$140>=$A$2+105,IF
($A$2:$A$140<=$A$2+139,IF
($B$2:$B$140=$AC2,$AA$2:$AA$140))))

Now select the range AD2:AG2 and double click the fill
handle to copy the formulas and fill the data table with
the averages.

Biff

 

[hgrove]

Biff wrote...
...

Col A = date
col B = weekday
Col C:Z = 24 hourly values
Row 1 A1:Z1 = headers

So, the actual data is in the range A2:Z141

In AA2 enter: =MAX(C2:Z2)

...

This is a fundamental difference between our approaches. I had take
the OP at face value and assumed the maximums would be along the line
of

=MAX(C1,C8,C15,C22,C29)

i.e., the maximum for the same day of the week from a 5-week period fo
each hour of the day separately.

We can't both be right, but only the OP could say for sure which of u
understood the specs correctly

 

[Biff]

It is frustrating when there are replies with differring
interpretations and no feedback from the Op.

Biff

 

[Gassy]

Guys
I am sorry I have not replied sooner but have not been able to get to an
internet connection until now (UK time). I am really appreciative of your
input, hgrove has nailed the question in that I need to average the maximum
for each 5 week period for every column i.e. hour of day and split into day
of week. So function could be =Average(Max(C2,C9,C16,C23,C30),Max(C37.....
etc. This would work but i was wondering whether a formula could be written
so that it looks for the day of the week and does the calculation based on
that parameter. Hope this is clearer.
Thanks again

Mark

 

[hgrove]

Gassy wrote...
...

. . . So function could be
=Average(Max(C2,C9,C16,C23,C30),Max(C37..... etc.
This would work but i was wondering whether a formula could
be written so that it looks for the day of the week and does the
calculation based on that parameter. Hope this is clearer.

...

I had misread your specs in my original reply. Using the same setup a
in my first reply to you (entire table named TBL, day of week specifie
by WD), you could use formulas like

=AVERAGE(LARGE(N(OFFSET(TBL,MATCH(WD,OFFSET(TBL,0,1,7,1),0)+{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;
16;17;18;19}*7-1,2,1,1))+10^(6+{0;0;0;0;0;1;1;1;1;1;
2;2;2;2;2;3;3;3;3;3}),{1;6;11;16})-10^(6+{0;1;2;3})