How to average right result with missing values in Excel?
When working with data in Excel, it is common to encounter ranges that contain missing values, or cells filled with error indicators such as #N/A or #DIV/0!. If you use Excel's standard AVERAGE function on such a range, the formula will return an error instead of ignoring the problematic cells. This means your calculations can become invalid or unusable whenever your data is incomplete or contains errors, as shown in the screenshot below. Being able to accurately calculate the average while skipping these missing or error values is crucial, especially in business reports, academic research, or any scenario with large and complex datasets. Below, you will find practical solutions and step-by-step instructions to calculate the correct average in such situations, ensuring the integrity of your data analysis.
Average with missing value
To calculate an average in Excel while skipping missing values or error cells, you can choose from several formulas that intelligently ignore non-numeric and error cells. This is essential in scenarios such as survey results analysis (where some entries may be incomplete), sales data over time (with blank months), or scientific data logging (where certain measurements might be missing or invalid). Below are two reliable formula solutions and their details.
Option 1: AGGREGATE Function
The AGGREGATE function is particularly suited for situations where you expect errors or hidden values in your data range, as it allows you to configure which types of data to ignore. This is handy when consolidating data from multiple sources or when the dataset may be updated frequently with inconsistent entries.
1. Select the cell where you want to display the average result, and enter the following formula:
=AGGREGATE(1,6,A2:C2)Here, 1 specifies the AVERAGE operation, and 6 tells Excel to ignore error values in the specified range A2:C2.
2. After entering the formula, press Shift + Ctrl + Enter (for legacy Excel versions), or simply press Enter if you are using Excel365 or Excel2019, as these versions support dynamic arrays without extra keystrokes.
Advantages: Easy to use; directly ignores errors and returns accurate results. Works well for ranges that include error values.
Limitations: Only available in Excel2010 and later. Older versions of Excel do not support the AGGREGATE function.
Option 2: AVERAGE with IF and ISNUMBER Array Formula
Alternatively, you can calculate the average of only numeric values (automatically skipping errors and blanks) using an array formula. This approach is suitable when you want to ensure only valid numbers are considered, which is common in monthly reporting, grading systems, or finance sheets.
1. In the cell where you want your average result, type this formula:
=AVERAGE(IF(ISNUMBER(A2:C2),(A2:C2)))This formula tests each cell in A2:C2, keeps only the numeric values, and calculates their average.
2. Once entered, hold the Ctrl and Shift keys and then press Enter to turn it into an array formula in older Excel versions (confirmed by curly brackets appearing in the formula bar). In Excel365 and Excel2019, simply pressing Enter is sufficient.
Tip: If your data spans multiple rows and you need results for each row, use the fill handle (the small square at the bottom-right corner of the selected cell) to drag the formula down. This automatically adapts the references for each row.
Advantages: Works for all Excel versions; ignores non-numeric values and errors; reliable for a variety of error or missing value scenarios.
Limitations: Requires input as an array formula in older Excel versions, which can be confusing. Mistyping can lead to a #VALUE! error if entered differently.
No matter which method you choose, make sure the targeted range accurately includes only the desired data—unintended extra columns or rows may skew results. If you encounter persistent errors, double-check your formula entry method (especially array formulas in legacy Excel), ensure that you have correctly selected the range, and verify that there are no merged or hidden cells that may disrupt calculation.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in