HOME > SOFTWARE > EXCEL
How to ignore #DIV/0! and zeros in Excel with AVERAGE()
The AVERAGE function has a problem generating a result from a column that presents errors, such as the famous #DIV/0!, or zero values. How can one ignore these cells and get a correct result?
The solution remains in the following formula:
=AVERAGEIF(I2:I297;"";0;I2:I297)
Where I2: I297 is the column with the data you want to compile.
Note: Commissions may be earned from the links above.
This page contains references to products from one or more of our advertisers. We may receive compensation when you click on links to those products. For an explanation of our advertising policy, please visit this page.
Related posts:
How to ignore #DIV/0! and zeros in Excel with MEDIAN()
https://w3ask.com/ignore-div-zeros-excel-median/
https://w3ask.com/ignore-div-zeros-excel-median/
With the AVERAGE functions, either AVERAGE () or MEDIAN (), we have very quickly a problem when our data contains errors, for example zero values ...
How to remove #DIV/0! and zero values in Excel?
https://w3ask.com/remove-div-values-excel/
https://w3ask.com/remove-div-values-excel/
When creating a table, with data and then calculations on this data in Excel, we can obtain cases with the value zero or the famous message ...
How to get the average of the last 5 values in Excel
https://w3ask.com/average-last-5-values-excel/
https://w3ask.com/average-last-5-values-excel/
How to obtain the average of the last x cells in the Excel software and thus see if there is an evolution with our data compared to other averages, ...
How to have a better average in Excel?
https://w3ask.com/average-excel/
https://w3ask.com/average-excel/
With the functions of average in Excel, we quickly notice their limitation. For example AVERAGE() gives an overall average that can be highly ...
Excel macro to insert a picture with Redime
https://w3ask.com/excel-macro-insert-picture-redime/
https://w3ask.com/excel-macro-insert-picture-redime/
You want to save a macro that will allow you to insert a picture (located in a directory of your hard drive disk) in an active cell where you ...