Excel Formula for Rolling Daily Average Excluding Weekends and Holidays

Objective

To calculate a rolling daily average for a specified month while excluding weekends and holidays.

Formula Overview

The formula utilizes the AVERAGEIFS function combined with WORKDAY to ensure that only working days are considered. Here’s how the components of the formula work together:

1. Date Range: Identify the range of dates for the month.
2. Values Range: Capture the corresponding values for those dates.
3. Criteria: Apply criteria to exclude weekends and holidays.

Formula

Assuming:

• Your dates are in Column A (A2:A31)
• Your values to average are in Column B (B2:B31)
• A list of holidays is specified in another range, for example, D2:D10

The formula for a rolling average at the end of the month (for instance in cell C31) would be structured as follows:

=AVERAGEIFS(B2:B31, A2:A31, ">=" & WORKDAY(EOMONTH(A31, -1) + 1, -1), A2:A31, "<=" & A31, A2:A31, "<>" & C2:C10)

Explanation of the Formula

• AVERAGEIFS: This function calculates the average of a range based on multiple criteria.
• B2:B31: Range containing the values that need to be averaged.
• A2:A31: Dates corresponding to the values.
• ">=" & WORKDAY(EOMONTH(A31, -1) + 1, -1): This criteria selects only dates that are greater than or equal to the last working day of the previous month, ensuring the start date is a working day.
• "<=" & A31: This criteria selects dates up to the current date.
• A2:A31, "<>" & C2:C10: This criteria excludes dates that match those in the holiday list (D2:D10).

Practical Example

Assume the following data:

And your holiday list in Column D could be:

Implementation

1. Place the dates into Column A and the corresponding values into Column B.
2. Enter your holiday dates into Column D.
3. In cell C31, paste the provided formula to calculate the rolling average.

Conclusion

This approach allows you to effectively compute a rolling daily average while accommodating non-working days such as weekends and designated holidays. For further enhancement of your Excel skills, you may explore resources on the Enterprise DNA Platform.