Daily Average Calculation Excluding Weekends and Holidays

Objective

To calculate the daily average of a data column based on monthly dates while excluding weekends and specified holidays in Excel.

Excel Formula Overview

The following formula is utilized:

=AVERAGEIFS(Data_Range, Date_Range, ">=start_date", Date_Range, "<=end_date", Date_Range, "<>Sat", Date_Range, "<>Sun", Date_Range, "<>Holiday1", Date_Range, "<>Holiday2",...) 

Components Explained

1. Data_Range: The range of cells containing the values for which you want to calculate the average (e.g., sales figures).
2. Date_Range: The range of cells containing the dates corresponding to the data in Data_Range.
3. start_date: Starting date of the month for which the average is calculated.
4. end_date: Ending date of the month for which the average is determined.
5. <>Sat and <>Sun: Filters to exclude Saturdays and Sundays from the calculation.
6. <>HolidayX: Filters for any holidays you wish to exclude; additional holidays should be added as necessary.

Practical Example

Suppose you have the following data:

Step 1: Define the Ranges

• Data_Range: B2:B9
• Date_Range: A2:A9

Step 2: Specify Start and End Dates

• start_date: 01/01/2023
• end_date: 01/10/2023
• Holidays: For this example, 01/09/2023 is a holiday.

Step 3: Construct the Formula

The formula to calculate the daily average excluding weekends and the specified holiday is:

=AVERAGEIFS(B2:B9, A2:A9, ">=01/01/2023", A2:A9, "<=01/10/2023", A2:A9, "<>Sat", A2:A9, "<>Sun", A2:A9, "<>01/09/2023")

Conclusion

This formula effectively calculates the daily average of sales values for January 2023 while excluding weekends and the specified holiday. It is a powerful method for ensuring that the resulting average reflects only valid business days. For enhanced skills in using Excel for data analytics, consider exploring resources available through the Enterprise DNA Platform.