To find the highest average in Excel when you have several groups, calculate an average for each group with AVERAGEIF, then use MAX on the average results. For example, you can compare average test scores by class or average sales by department. The steps below show how to calculate the group averages, identify the largest one, and find which group earned it.
What You’ll Need
- A worksheet with a group or category in one column and the numeric values to average in another.
- A small summary area where you can list each group and calculate its average. Excel for Microsoft 365 on Windows is used for the directions; the formulas also work in current Excel for Mac.
The example uses departments in column A and scores in column B. You can replace those labels and ranges with your own data. If you only need the average of one set of values, use Excel’s AVERAGE function; this guide focuses on finding the highest average among multiple groups.
Step-by-Step
-
Check and arrange your source data. Put one observation per row, with a department, class, product, or other group in column A and the value you want averaged in column B. Add descriptive headings in row 1, such as Department and Score. Avoid merged cells and blank rows within the data. For the worked example, the rows are:
- Sales: 80, 100, and 90
- Support: 90, 70, and 80
- IT: 90 and 80
Each row represents one observation. Do not calculate an average of the group averages unless every group has the same number of observations; averaging the original records by group gives the correct group-level result.
-
Create a list of the groups you want to compare. In an empty area, enter Group in D1 and Average in E1. Type Sales in D2, Support in D3, and IT in D4. Include every group that should be part of the comparison, but list each group only once. This list is the basis for the summary and helps make it clear which groups are included.
If you have a long list, you can copy the group column to the summary area and use Data > Remove Duplicates on the copied list. Make sure you select only the copied list, not the original records. If you already maintain a clean category list, use that instead.
-
Calculate the first group’s average. With source departments in A2:A9, source scores in B2:B9, and the group name Sales in D2, enter this in E2:
=AVERAGEIF($A$2:$A$9,D2,$B$2:$B$9)AVERAGEIF checks the department column for the value in D2 and averages the corresponding scores. The dollar signs keep the source ranges fixed when you copy the formula. In this example, Sales has scores 80, 100, and 90, so E2 returns 90.
-
Copy the formula for the other groups. Select E2 and drag the small square at the lower-right corner of the cell down through E4, or copy E2 and paste it into E3:E4. Excel adjusts the group reference from D2 to D3 and D4 while keeping the source ranges fixed. The summary should show Sales at 90, Support at 80, and IT at 85. Check that each result makes sense before moving on.
For example, Support’s scores total 240 across three records, giving an average of 80. IT’s scores total 170 across two records, giving an average of 85. The group with fewer records can still have the highest average; the number of observations does not determine the ranking by itself.
-
Return the highest average. With the three calculated averages in E2:E4, enter this in an unused cell, such as G2:
=MAX(E2:E4)MAX returns the largest number in the summary. With the example values 90, 80, and 85, G2 returns 90. This is the highest average, not the highest individual score in the source data.
-
Find the group associated with that average. With group names in D2:D4 and their averages in E2:E4, enter this in G3:
=INDEX(D2:D4,MATCH(MAX(E2:E4),E2:E4,0))MATCH locates the first occurrence of the maximum average, and INDEX returns the group name from the same position. In this example, G3 displays Sales. If two or more groups share the maximum, this formula returns the first matching group in the summary list; use the tie-checking advice below if you need to display all winners.
-
Label and verify your results. Add labels such as Highest average and Winning group beside the results so a reader knows what G2 and G3 mean. Confirm that the summary includes all relevant groups, that the original values are numeric, and that the average cells display the expected values. If you change source records, the averages and maximum update automatically as long as the formulas’ ranges include the changed rows.
On Excel for Mac, enter and copy the same formulas; the worksheet functions work the same way. Ribbon labels can vary slightly by release, but no special Windows-only feature is required for this method.
Common Mistakes
-
Using MAX on the raw values. MAX of B2:B9 would return 100 in the example, which is the highest single score. It does not compare average scores by group. Apply MAX to the calculated averages instead.
-
Leaving out a group or listing it twice. The result only compares the groups in D2:D4. A missing group cannot win, while a duplicate group can make the summary confusing and may lead to misreading the results. Check the list against the source data.
-
Using mismatched ranges. The criteria range and average range in AVERAGEIF must cover corresponding rows. If the department range starts at row 2 but the score range starts at row 3, Excel compares the wrong records. Keep both ranges the same height and aligned.
-
Including a heading or unrelated cells in the maximum range. MAX ignores text in a range, but stray numbers or old results can still become the reported maximum. Keep the summary averages together and use only their cells in the MAX formula.
-
Assuming blanks and zeros mean the same thing. AVERAGEIF ignores blank cells in the average range, but it includes numeric zeros. If zero means missing data in your worksheet, decide how to handle that before comparing the groups; do not silently treat a real zero as blank.
Tips
-
Use an Excel Table for data that grows. Select a cell in the data and choose Insert > Table, then confirm that the table has headers. Tables expand when you add rows, which can make your analysis easier to maintain. If you use ordinary cell ranges as in this guide, update the ranges when new records are added beyond row 9.
-
Make the comparison easy to scan. Format the average column consistently using Home > Number, and choose an appropriate number of decimal places. Rounding the display does not change the underlying values, so two averages that look equal when rounded might not actually tie.
-
Check for ties explicitly. With summary averages in E2:E4 and the maximum result in G2, enter this in G4 to count how many groups equal the maximum:
=COUNTIF(E2:E4,G2)A result greater than 1 means the highest average is shared. To list every winning group, inspect the summary rows whose averages equal G2, or filter the summary by the maximum value.
-
Use filters or a chart for larger summaries. Sort the summary by Average from largest to smallest to see the ranking, or select the summary and choose Insert > Recommended Charts to make a quick comparison chart. Sorting is helpful for reviewing every group, while MAX remains the direct way to return the top number.
Troubleshooting
AVERAGEIF returns #DIV/0!. This usually means there were no qualifying numeric values to average. Check that the group name in D2 matches a value in the source department column, and check that the matching score cells contain numbers. Extra spaces, misspellings, or numbers stored as text can prevent the expected matches or averages.
The answer is unexpectedly low or high. Check for omitted records, incorrect range endpoints, zeros that should be treated differently, and accidental numbers in the summary range. Click an average cell and verify that the formula points to the intended criteria and score columns. If new records were added below row 9, extend the source ranges or convert the data to a Table and adjust the analysis accordingly.
Several results look equal, but the tie count is one. The cells may only look equal because the displayed decimals are rounded. Increase the displayed decimal places temporarily and compare the underlying values. If the values differ slightly, decide whether your reporting rules call for exact values or a chosen rounding precision before declaring a tie.
The group name formula returns the wrong winner. Confirm that its group range and average range start and end on the same rows. Also check whether the maximum cell refers to the current summary rather than an older or unrelated result. When there is a tie, INDEX and MATCH return the first match, so review the other matching rows as well.
Excel shows the formula as text instead of calculating it. Select the cell, set its number format to General from Home > Number, then press F2 and Enter to re-enter the formula. Also check that the formula begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut may differ by keyboard layout, so use Formulas > Show Formulas.
Frequently Asked Questions
How do I find the highest average in one column without groups?
If you simply want the average of one set of values, use AVERAGE on that range; there is only one average to report. To find the highest average across several separate columns or groups, calculate each average first and take the maximum of those results.
Can I find the highest average without making a summary table?
A summary table is usually the clearest and easiest method to audit, especially when you need to see which groups were compared. A compact formula is possible, but it is harder to check and can be less convenient when groups change. For a reusable worksheet, the visible summary makes the logic transparent.
Does AVERAGEIF include zero values?
Yes. A numeric zero that meets the group criterion is included in the average. Blank cells and text values in the average range are not counted as numeric observations. Check what zeros mean in your data before interpreting the result.
What if two departments have the same highest average?
The MAX result still shows the shared average. The INDEX and MATCH formula shown above returns the first matching department in the summary list. Use the COUNTIF tie check to confirm there is more than one winner, then identify all rows with that average.
Can I compare averages that have different numbers of records?
Yes, AVERAGEIF calculates each group’s mean from its matching numeric records, even when group sizes differ. Remember that a mean based on few observations may be less representative than one based on many; the spreadsheet calculates the comparison but does not judge how reliable the groups are.
Will the result update when I add more data?
It updates when the new rows are inside the ranges used by the formulas. With fixed references ending at row 9, records entered on row 10 are not included. Extend the source ranges or use an Excel Table and adjust the formula setup so new observations are covered.
Related
- How to Export an Excel Table to Word Without Losing Format
- MT5 History to Excel: Export Trades and Account Data
- Export an Excel Table as an Image: Step-by-Step
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find Shortcut: How to Search a WorksheetIn Excel for Microsoft 365 on Windows, press Ctrl+F to open Find, type the text or number you…
- Google Sheets Timesheet Template: Setup, Formulas and TipsTo make a timesheet template in Google Sheets, create columns for the date, start time, end time, unpaid…
- Excel File Corrupted? Repair and Recover Your WorkbookIf an Excel file is corrupted, first make a copy of it, then try Excel for Microsoft 365…
- Excel Shortcut Keys for Switching Between WorksheetsIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next worksheet tab, or…
- How to Remove Prior Formatting in Microsoft Word EasilyTo remove prior formatting in Word, select the affected text, then choose Home > Font > Clear All…
- Data Validation Excel Shortcut and How to Use ItIn Excel for Microsoft 365 on Windows, press Alt, then A, V, and V—one key at a time—to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.