How to remove 0 when calculating average value in excel

When averaging in Excel, you can ignore 0 values ​​through the following two functions: AVERAGEIF() function: Specify conditions to exclude 0 values, the syntax is AVERAGEIF(range, condition, average range). AVERAGEA() function: automatically ignore empty cells and text values, the syntax is AVERAGEA (range).

How to ignore 0 when calculating the average in Excel

When calculating the average in Excel, you can pass Use a specific function to ignore 0 values.

Use the average function that ignores 0 values

There are two functions in Excel that can be used to find the average and ignore 0 values:

• AVERAGEIF(): Calculate the average based on the specified conditions. Syntax: AVERAGEIF(range, condition, average range)
• AVERAGEA(): Calculate the average of all numbers in the range, ignoring empty cells and text values. Syntax: AVERAGEA(range)

Steps

When using the AVERAGEIF() function, you can specify conditions to exclude 0 values. For example, the following formula will calculate the average of numbers in the range A2:A10, ignoring 0 values:

<code>=AVERAGEIF(A2:A10, "<>0", A2:A10)</code>

The AVERAGEA() function is a simpler method because it does not require specifying conditions and automatically ignores nulls Cell and text values:

<code>=AVERAGEA(A2:A10)</code>

Example

Assume the data in the range A2:A10 is as follows:

<code>A2: 10
A3: 20
A4: 30
A5: 0
A6: 40
A7: 50
A8: 0
A9: 60
A10: 70</code>

Use the AVERAGEIF() function to ignore 0 Calculate the average of the values:

<code>=AVERAGEIF(A2:A10, "<>0", A2:A10)</code>

Result: 35

Use the AVERAGEA() function to ignore 0. Calculate the average of the values:

<code>=AVERAGEA(A2:A10)</code>

Result: 35

The above is the detailed content of How to remove 0 when calculating average value in excel. For more information, please follow other related articles on the PHP Chinese website!