Average weekly maximum


J

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
 
Ad

Advertisements

S

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?
 
J

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.
 
Ad

Advertisements

T

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top