Average if day is Monday and within last 6 months

[stiggz]

So I have spent about 4 hours trying different combinations to get the result I am after.

I have a spreadsheet that automatically fills data from a separate CSV generated daily (data is added on to end on a day by day basis)

Column A is the Date, Column B is the values I want to average

Tried a variety of formulas but this is where my head was when I started

=AVERAGEIFS(B:B,A:A,(TODAY()-183),WEEKDAY(A:A),7)

I assume SUMPRODUCT would be the method to use but I haven't wrapped my head around that yet

[FDibbins]

Hi and welcome to the forum

Not sure if you will be able to do this or not, but I would add a helper column (use C?) and copy the =weekday(a1,2) down as far as you need it. then base your criteria for the weekday (Mon=1) on that?

[:) Sixthsense :)]

Or

Please Login or Register  to view this content.

[stiggz]

@Sixthsense - This doesn't appear to work for me, producing DIV error.

Attached the sheet im working on

AvgPerDay.xlsx

[:) Sixthsense :)]

The formula should be

Please Login or Register  to view this content.

1st row contains Column Headers which is TEXT characters and in A987 cell there is a text value called Grand Total

[Kevin UK]

Hi stiggz

Would it not be easier to have a helper column for the weekday of the dates in column A. Then you could use the AVERAGEIFS function. I have used column C>

Please Login or Register  to view this content.