Average Last 7 Days by Weekday/Weekend

Hi,

I am trying to create a formula that will automatically average data from the last 7 days in a table based on whether or not it's a weekday or weekend. I have to update this on a weekly basis and want the template to pull the last 7 days without having to manually change the table array which is why I'm not just using an averageifs tied to the value in column a. I've attached an example file on what I'm working with.

I have tried a variation of different averageifs and offsets and while I can get some formulas to work, I cannot think of a way automatically select the last 7 days and get it to average based on whether or not it's a weekday or weekend. Essentially, I want the formula =average(offset(c1,counta(c1:c31)-7,0,7) to average based on weekdays vs weekends.

Any input would be greatly appreciated and thank you in advance!!

Using your posted workbook and assuming all days of the month are listed once,here's one way:
This regular formula returns the average of the last 7 Col_C non-blank values where the corresponding Col_B date is a weekday

Please Login or Register  to view this content.

For your data, that formula returns: 17,309
(13154;21354;45641;13154;1052;13654;13154)/7

Is that something you can work with?

Hi Ron,

Thanks for your reply! I should clarify I'm not looking for an average of the last 7 weekdays, rather of the last 7 days an average for the 5 weekdays and the 2 weekend days. So using the data from the posted workbook essentially two formulas that yield:

• Weekday:$17,331 (45641,13154,1052,13654,13154)/5
• Weekend:$33,498 (21354,45641)/2

The simple solution is to use an averageifs and a ">5" and "<6" however I don't know how to get it to auto populate based on the bottom 7 cells.

Last 5 weekdays:

Please Login or Register  to view this content.

Last 2 weekend days:

Please Login or Register  to view this content.

Does that help?

Well, that's a different scenario. It appears that you add dates as you add data, so you have blank cells below the data range. The previous formula I posted gets tripped up by those blank cells because the FREQUENCY function is returning fewer values than there are rows referenced in I2:I32.

Try these regular formulas:

Weekend average

Please Login or Register  to view this content.

Weekday average

Please Login or Register  to view this content.

In your example...
Weekend average = 13,399
Weekday average = 43,327.5

Does that help?

Yikes...I merged portions of the two formulas...and even mis-labeled them in my post.

These are the corrected formulas:
Weekend average

Please Login or Register  to view this content.

Resolves to: 33,497.50

Weekday average:

Please Login or Register  to view this content.

Resolves to: 17,331.00

Better?

I'll use the weekend formula
=SUMPRODUCT(($B$2:$B$32=LARGE(INDEX((WEEKDAY($B$2:$B$32,2)>5)*$B$2:$B$32,0),{1,2}))*$I$2:$I$32)/2

The LARGE function returns the 1st, 2nd, or whatever largest item in a list.
If A1:A5 contains this list
10
2
6
1
4

=LARGE(A1:A5,3) returns: 4 (the 3rd largest value)

However, if you entered this in a cell:
=LARGE(A1:A5,{1,2,3})

the result would be an array of the top 3 largest values.
You can check by:
• select the cell containing that formula
• press F2
• press F9
the result in the formula bar with be: {10,6,4}
• Press ESC to restore the formula

The INDEX function typically references ranges as if the range was the entire spreadsheet.
This formula:
=INDEX(C10:E15,2,3)
would reference the 2nd row, 3rd column of C10:E15

However, when used like I did in the formula I posted (with a zero as the last parameter), it forces Excel to treat the referenced range as an array...eliminating the need to create an array formula (which you'd complete by holding down CTRL and SHIFT when you press ENTER, instead of just pressing ENTER)

So this section of the formula:
INDEX((WEEKDAY($B$2:$B$32,2)>5)*$B$2:$B$32,0)

tests if the weekday of each cell in $B$2:$B$32 is greater than 5 (and therefore a Saturday or a Sunday)
If Yes...the result is TRUE...which Excel sees as 1.
If No....the result is FALST..which Excel sees as 0.

When that array of 1's and 0's is multiplied times the dates in $B$2:$B$32,
the result is a series of weekend date serial numbers and zeros.

The LARGE function returns the top 1 and 2 values.
The SUMPRODUCT function sums those top 2 values.
That sum is then divided by 2 to calculate the average.

I hope that helps.