Excel formula for average based on criteria

Excel formula for average based on criteria

The AVERAGEIF function is one of the most useful functions since it enables Excel users to perform averages only on cells that meet the specified criteria. This function accepts a minimum of two parameters. However, you can choose to add the third parameter if you like. The compulsory parameters include Range and criteria. The range which is the first parameter represents the block of cells within which the criteria will be tested. The second parameter is the criteria. The criteria represent the condition or test being applied before finding the average. The third parameter which is usually not mandatory represents the range of cells bring averaged. Therefore, the general syntax of an Average if would be as follows:

=AVERAGEIF(RANGE, CRITERIA, optional Range)

Example of AVERAGEIF () with two parameters

We can, for example, calculate the average of all cells whose values are greater than five using the following formula:

=AVERAGEIF(F8:F19,”>40″)

1. Example of AVERAGEIF with three parameters:

Finding average for non-empty cells:

Suppose we want to find the averages of cells that have content in them. We would place the <> inside as the parameter for criteria. The formula would look as follows:

=AVERAGEIF(E8:E19,”<>”,F8:F19)

Note: when all three parameters are supplied, the third parameter represents the cells which are to be averaged.

2. Excel formula to find the average of any other cell

The AVERAGEA formula is used in Excel to find the average every other nonempty Cell. Its syntax is as easy as that of average (). However, this formula will constantly follow a set of rules when calculating the average. These include the evaluation of Boolean TRUE as a 1 while the FALSE Boolean evaluates to zero, empty cells are discarded, while textual values(strings) are assigned a zero too. This means that for every non-empty non-integer cell, a given value is added being part of the values to the total before the average is performed.

Example of AVERAGEA ()

=AVERAGEA(G7:G17)

3. Excel formula for average growth rate.

We can also calculate the growth late using Excel as follows:

1. Identify the number of years of growth. Find the growth rate for successful years.

2. Type in the following formula to find the average of the growth rates.

=AVERAGE(G7:G14)

3. Press enter.

4. Save changes.