Average weekly maximum

[JD]

A1-A31 corresponds to days of the month. 3 to 4 times a week lab data is
entered into the corresponding cells B1-B31, some are less than values (<2).
I want to calculate the maximum weekly average, i.e. the highest value of the
4 or so weekly averages and put it at the bottom of the column. I don't want
a column or cell of weekly averages and the <2 values should be counted as 2.

A1 2
A2 2
A3
A4
A5
A6 <2


A8 6
A9
A10 2
A11
A12 <2
A13
A14 2

Answer = 3 (for this 2 week period)

Thanks

 

[Sheeloo]

Your question is not clear.

What I got -
If value is <2 then take it as 2...
take average of values for days 1-7, 8-14, 15-21, 22-29 and then find the
maximum of these four values, right?

 

[JD]

That is correct. But I do not want to include an extra column in my
spreadsheet for the weekly averages. Any help is greatly appreciated.

 

[T. Valko]

There is no "elegant" way to do this.

Without using a helper column you'd need to use an array formula** something
like this:

=MAX(formula_week1,formula_week2,formula_week3,formula_week4,formula_week5)

And each weekly formula would look like this (adjusting the range for each
week):

IF(COUNTA(B1:B7),AVERAGE(IF(B1:B7<>"",--MID(B1:B7,(LEFT(B1:B7)="<")+1,10))),0)

So, imagine what 5 of those strung together will look like!

If I were you I'd use a helper column and put this array formula** in C7,
C14, C21, C28 and C31:

=IF(COUNTA(B1:B7),AVERAGE(IF(B1:B7<>"",--MID(B1:B7,(LEFT(B1:B7)="<")+1,10))),0)

Then use:

=MAX(C1:C31)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)