AVERAGE last 4 Mondays?

[Ted]

I hope I am not coming to the well too often but the help people provide
here is incredible.

I have a data sheet that will not be seen by users. They input data daily
using a form. Column A has the date and B and C have numbers associated
with that particular date.

Data is entered daily but may always not be in order. The users are not
able to sort the sheet. Since data are entered daily the number of rows of
data will continue to grow over time.

Can someone suggest a formula that will look through all the data and show
the average for the last 4 mondays? I will need to do the same for each day
of the week.

Thank you

 

[Mike H]

Ted,

Not extensively test but seems ok. Dates in Col A and data in Col B. Note it
ignores any date in the future and any valid date if there are no data
entered in Col B. The formula as posted does the last 4 Sundays. Enter as an
ARRAY and drag down 6 cells for the other 6 days of the week

=AVERAGE(IF(WEEKDAY($A$1:$A$100)=ROW(A1),IF($A$1:$A$100<=TODAY(),IF($A$1:$A$100>TODAY()-28,IF($B$1:$B$100<>"",$B$1:$B$100)))))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.