Excel: Calculate Average if Greater Than Zero

You can use the following formula to calculate the average in Excel only for values that are greater than zero:

=AVERAGEIF(B2:B14, ">0", B2:B14)

This particular formula calculates the average value in the range B2:B14 only for the cells that have a value greater than zero.

The following example shows how to use this formula in practice.

Example: Calculate Average if Greater than Zero in Excel

Suppose we have the following dataset in Excel that shows the total sales made by various employees at a company:

If we simply used the AVERAGE() formula, we would find the average sales for all of the employees:

The average sales per employee is 3.307.

However, suppose we wanted to only calculate the average for employees who had greater than zero sales.

We could type the following formula into cell D2:

=AVERAGEIF(B2:B14, ">0", B2:B14)

The following screenshot shows how to use this formula in practice:

Excel average if greater than zero

The average sales for employees who had greater than zero sales was 4.3.

This formula calculated the average by only using the values that were greater than zero.

We can confirm that this is correct by manually calculating the average of all values that are greater than zero:

Average of Values Greater than Zero: (10+4+4+3+9+2+1+1+4+5) / 10 = 4.3.

This matches the value calculated by our formula.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Calculate Average If Cell Contains Number in Excel
How to Use AVERAGEIF with Multiple Ranges in Excel
How to Calculate Average Excluding Outliers in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *