Formula Generator | Excel

Daily Average Calculation in Excel Excluding Weekends

This guide provides an Excel formula using AVERAGEIFS and NETWORKDAYS to compute daily averages of data for March 2023, excluding weekends. It includes a breakdown of the formula components and a practical example.

Prompt

Answer

Calculation of Daily Average Excluding Weekends in Excel

Objective

To create an Excel formula that calculates the daily average of a specified column based on monthly dates while excluding weekends.

Formula Breakdown

AVERAGEIFS Function: This function calculates the average of a range based on multiple criteria.
NETWORKDAYS Function: This helps to exclude weekends from the date range.

Assumptions

Your date data is in column A (A2:A30).
Your numeric data for which the daily average needs to be computed is in column B (B2:B30).
You want the daily average for a specific month, for instance, March 2023.

Formula

To calculate the daily average for March 2023 excluding weekends, use the following formula:

=AVERAGEIFS(B2:B30, A2:A30, ">=2023-03-01", A2:A30, "<=2023-03-31", A2:A30, "<>""", WEEKDAY(A2:A30, 2), "<=5")

Explanation of Formula Components

B2:B30: This is the range containing the values you are averaging.
A2:A30: This range contains the dates corresponding to the values in column B.
">=2023-03-01": This criteria specifies the start of the month.
"<=2023-03-31": This criteria specifies the end of the month.
"<>": This ensures that blank cells in the date column are ignored.
WEEKDAY(A2:A30, 2): This function returns an integer representing the day of the week for each date, where Monday=1 and Sunday=7. We use <5 to exclude Saturday and Sunday.

Practical Example

Suppose your data is structured as follows:

A	B
Date	Value
2023-03-01	10
2023-03-02	20
2023-03-03	30
2023-03-04	40
2023-03-05	50
2023-03-06	60
2023-03-07	70

From the above data, use the formula to calculate the daily average excluding weekends.
The formula will only consider data from March 1st to March 31st on weekdays.

Final Result

This formula will provide the average of all values from column B where corresponding dates in column A fall within March 2023 and are not weekends.

Conclusion

By using this method, accurate daily averages can be obtained efficiently, allowing for effective analysis and reporting in Excel while excluding non-working days. If further learning on Excel functionalities is desired, consider exploring courses available on the Enterprise DNA Platform.