Pivot Table Issues

How to Calculate Average Appointments by Weekday in Excel Pivot Tables

Olivia Miller Oct 10, 2026 868 views

Question details

The user needs to calculate the average number of appointments or assessments for each day of the week without encountering calculation errors in standard pivot tables.

How to Calculate Average Appointments by Weekday in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to group appointment data by weekday and find the daily average using a standard Excel PivotTable.
Observed behavior: The standard PivotTable returns a #DIV/0! error or invalid average calculations when attempting to average the grouped weekday records.

Before you start

Ensure your source data is formatted as an official Excel Table (Ctrl+T) and that your date column contains valid date values rather than plain text.

Solution 1Recommended

Use Power Query to Group Records and Calculate Averages

Power Query efficiently transforms and groups data, allowing you to calculate valid weekday averages without triggering DIV errors in a standard PivotTable.

In Excel 365 and newer versions, replacing complex worksheet formulas with Power Query transformations is highly recommended. It processes data cleanly and preserves correctly formatted source columns.

Load Data into Power Query

Select your data table, navigate to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

Extract the Weekday

Select your Date column. Go to the Add Column tab, click 'Date', navigate to 'Day', and select 'Name of Day'. This creates a new column with the weekday names.

Group by Weekday

Select the newly created Name of Day column. Go to the Home tab and click the 'Group By' button.

Configure the Average Calculation

In the Group By dialog, name the new column 'Average Appointments', select 'Average' as the operation, and choose the column containing your appointment counts. Click OK.

Load Data Back to Excel

Click 'Close & Load' on the Home tab to output your correctly averaged data into a new Excel worksheet.

Use Power Query to Group Records and Calculate Averages

Data Transformation Complete: Your data is now pre-aggregated. You no longer need to rely on the PivotTable's average function, eliminating the risk of DIV errors.

Solution 2

Use Power Pivot and DAX Measures

If you prefer to keep your underlying data ungrouped, you can use Power Pivot to create a DAX measure that iterates over the days and calculates the correct average.

Calculate with WPS Spreadsheet

Easily Calculate Weekday Averages Using WPS Spreadsheet Formulas

WPS Spreadsheet offers a fast, lightweight, and intuitive interface for data analysis. You can easily calculate average appointments by weekday using helper columns and standard PivotTables without dealing with complex Power Query tools.

1. Open Your Dataset: Launch WPS Spreadsheet and open your appointment data file.
2. Create a Weekday Helper Column: Add a new column next to your dates. Use the formula =TEXT(A2, "dddd") (assuming A2 is your date) and drag it down to extract the days of the week.
3. Insert a PivotTable: Highlight your entire data range, navigate to the Insert tab, and select PivotTable.
4. Configure Rows and Values: In the PivotTable Field List, drag your new Weekday column into the Rows area, and your Appointments Count column into the Values area.
5. Change Value Field Settings: Click the dropdown arrow on the Appointments field in the Values area, select 'Value Field Settings', and change the calculation from 'Sum' or 'Count' to 'Average'.

Free and lightweight alternative to Microsoft Excel.Fully compatible with Microsoft Excel (.xlsx) formats and standard PivotTables.Built-in dynamic array formulas and AVERAGEIF functions for quick calculations.Intuitive user interface with no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable show a #DIV/0! error when calculating averages?

This error usually occurs when the PivotTable attempts to divide by zero. In appointment data, this happens if a specific weekday has blank records, zero-value records, or if the calculation logic is improperly grouping textual data instead of numeric counts.

Do I need Excel 365 to calculate weekday averages?

While Excel 365 offers advanced dynamic array formulas that make this easier, you can still calculate weekday averages in older versions using Power Query, Power Pivot, or traditional helper columns combined with AVERAGEIF formulas.

How do I extract the day of the week from a date cell?

You can extract the day of the week by using the TEXT function. For example, typing =TEXT(A2, "dddd") in an adjacent cell will return the full name of the weekday (e.g., Monday, Tuesday) for the date located in cell A2.

Can I use Power Query to clean my source data first?

Yes, Power Query is highly recommended for cleaning data. You can replace complex worksheet formulas with Power Query transformations, ensuring your source data is properly formatted and free of blanks before loading it into a PivotTable.

Excel & Data Problems