W3Ask

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.

Excel VBA, test of the And (&)
https://w3ask.com/excel-vba-test-of-the-and/

In an Excel VBA, how to detect a cell with a & character in a For loop ? You don’t necessarily need a loop in that case, here is an example ...

How to remove #DIV/0! and zero values in 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 ...

Choice of a list with a condition in Excel
https://w3ask.com/choice-list-condition-excel/

There are two sheets, in which there is two colums : – from A1 to A3 with dates (example : 01/01/2016), – from B1 to B3 with titles ...

How to convert a number in time with Excel ?
https://w3ask.com/convert-number-in-time-excel/

When we enter a date with points, is there a possibility to convert it automatically into a date with slashes, so that it could be taken into ...

How to have a better average in 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 ...

How to ignore #DIV/0! and zeros in Excel with AVERAGE()

Related posts: