How to Average by Category in Excel with AVERAGEIF
Before scheduling repairs or writing a weekly report, calculate average repair time by category. Open your Excel work order sheet (.xlsx) in Cue Office and use AVERAGEIF to average the minutes for the category you select. A recorded zero counts in the average. A blank time is skipped.
How Do You Average Repair Time by Category?
In this example, column B contains the repair Category, column C contains the duration in Minutes, cell E2 specifies the category to summarize, and cell F2 displays the average. To follow along, paste the practice data into A1 of a new worksheet, where E2 is set to Lighting.
Copy the formula below, select F2, and paste it. B2:B7 is the category range, E2 is the category to match, and C2:C7 is the duration range for the same work orders.
=AVERAGEIF(B2:B7,E2,C2:C7)
When E2 is set to Lighting, the durations for M01, M03, and M05 are 12, 0, and 24 minutes. The formula sums these values and divides by 3, yielding an average of 12 minutes. M04 also belongs to Lighting, but its duration cell C5 is blank, so it is not included in the count.
Why Does Filling a Blank with Zero Change the Average?
Enter the number 0 into C5. The total duration for Lighting remains 36 minutes, but the count of tickets included in the average increases to four, changing F2 to 9.
Clear the 0 you just entered in C5 and verify that F2 returns to 12. Next, change E2 to Plumbing. The formula takes the 30 and 20 minutes from M02 and M06, resulting in 25. Save and reopen the workbook to verify the category, result, and formula.
How Can You Check That Cue AI Updates the Average?
Open an independent practice copy, ensuring that E2 is set to Lighting and F2 is empty. Click Cue AI on the Home tab.
Send the prompt below to clearly specify the calculation rules, destination cell, and dynamic update requirement.
What is the average repair time for the category selected in E2? Include recorded times of zero minutes and skip rows where the time is blank. Put the result only in F2 and make it update when I change the category in E2. Please keep the original work orders, times, category selection, and headings as they are.
Cue AI wrote an AVERAGEIFS formula in F2 only. It uses E2 to match categories in column B, then averages the repair times from those rows. Save, close, and reopen the copy. Select F2 to check the formula and the 12-minute Lighting average. C4 still contains a numeric zero, and C5 is blank.
In the reopened copy, change E2 from Lighting to Plumbing. F2 updates to 25 minutes, the average of the two Plumbing work orders at 30 and 20 minutes.
Practice with Work Order Data
Show the full example
Create a new spreadsheet in Cue Office and paste the following data into A1. Leave the duration in row M04 blank. Then return to the formula steps to calculate the value starting in F2.
Ticket Category Minutes Category Avg minutes M01 Lighting 12 Lighting M02 Plumbing 30 M03 Lighting 0 M04 Lighting M05 Lighting 24 M06 Plumbing 20
Check Missing Repair Times Before Sending the Weekly Report
Before you summarize this week’s repairs, tell Cue AI which column holds the categories, which column holds the repair times, and which category to average. When Lighting averages 12 minutes, check one work order recorded as zero and one with no time entered. Ask the person on shift to fill in the missing time, then update the report.
If a contractor sends a work order receipt (.pdf), open it in Cue Office for free and match the work order number to your Excel repair sheet (.xlsx) to verify the time. Record missing times and who will check them in a Word handoff log (.docx). In the PowerPoint weekly report (.pptx), list the average repair time by category and how many work orders still need a time entry. Cue Office offers free spreadsheet, document, and presentation editing.