Obtaining Peak Average High

  • Thread starter Thread starter Biff
  • Start date Start date
B

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
 
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)
Click to expand...
...

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
 
It is frustrating when there are replies with differring
interpretations and no feedback from the Op.

Biff
 
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
 
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.
Click to expand...
...

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})
 
Back
Top