How to average right result with missing values in Excel?
In some cases, if there are some missing values with errors in a range, the result will be error as well while using the general Average function as below screenshot shown in Excel. Here, I introduce the formulas that can ignore the missing values and average the right results in Excel.
quickly convert multiple XLSX. files to XLS or PDF. files in once time
|
| In many cases, you may want to convert between XLSX and XLS, or convert a workbook to PDF file, but in Excel, you only can convert one workbook once time, have you ever imaged to convert multiple workbooks at once time? With Kutools for Excel's File Format Converter, you can quickly convert multiple workbook to pdf/97-2003 workbook/2007 or higher workbook at once and save them in a folder, at the same time, a converted report is generated! Click for 60 days free trial! |
 |
| Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. |
Average with missing value
To average the right answer with missing values, you can use below formulas.
Select the cell you will place the result, and type this formula =AGGREGATE(1,6,A2:C2), press Shift + Ctrl + Enter keys.
Also you can use this formula =AVERAGE(IF(ISNUMBER(A2:C2),(A2:C2))), hold Shift key and press Ctrl + Enter keys.
If you need, drag fill handle down to fill cells with the formulas.
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!
