How to use AVERAGEIF MS Excel

While AVERAGE calculates average of all number in specified range, the AVERAGEIF formula is used in excel to calculate average of number which meets specified criteria.

Syntax of AVERAGEIF

formula is as below
=AVERAGEIF( Range, Criteria, Average_Range)

Range is range which contains number
Criteria is specification for a number in range to qualify for actual average
Average_Range is optional if provided will be used else Range will be used

This formula is compatible Excel 2007 onwards

AVERAGEIF use in calculating average ignoring zero in a range

1. Enter formula as =AVERAGEIF(B2:B7,”>0”)
The above formula will calculate average of cell with value greater than zero.

AVERAGEIF use in calculating average ignoring zero in a range

AVERAGEIF use in calculating average ignoring zero in a range

Other alternate of AVERAGEIF is AVERAGEIFS