Microsoft Excel – AVERAGEIFS, AVERAGEIF and AVERAGE functions

Microsoft Excel – AVERAGEIFS, AVERAGEIF, and AVERAGE functions are useful to calculate the Average value.

Microsoft Excel provides a good number of Functions to enable us to deal with Spreadsheet data. In this article, I am going to explain, AVERAGE, AVERAGEIF, and AVERAGEIFS functions. Before we start looking into these functions; lets’ understand, why an Average is required.

Why Average is required?

An average is required, to calculate the Central Value of the Data Set. For example, when you report your Company’s financials; you report Average data; instead of each and every detail. By seeing the Average results, people can understand; how the company is performing. Based on that the investors will start investing in the Company for growth. That means Average results are really helpful when showing financial results.

Another simple example is, we have 100 people, and everyone has different quantities of Apples. If someone asks, how many Apples the people have. Do you report the Average count? Or the Apple count each person has? We usually report the Average count. We calculate the Average, by summing all the Apples, divided by, the count of people (here it is 100). Now we know, on Average; each person has the count of Apples. This is easy to report; instead of reporting an individual’s Apple count.

Lets’ discuss, how we use them in Microsoft Excel.

AVERAGE function

Microsoft Excel provides the AVERAGE function; which gives the mean of the selected data.

Syntax: AVERAGE (number1, [number2], ...) OR
Syntax: AVERAGE (range)

For example, the average of 1, 2, 3, 4 is (1+2+3+4)/4 = 2.5. That means AVERAGE is the DIVISION of the SUM of the whole data set and COUNT of the whole data set. It considers, all the data set values to calculate the Average.

The AVERAGE function, allows multiple values separated by a comma (“,”) or range of values; to calculate the Average.

Lets’ take sample data; showing, which Positions the Sample Web Site pages are displaying in Search Results. We will calculate, Average Position, by using the AVERAGE function. These results are helpful to SEOs to optimize the Web Site for better positioning in Search Engine results.

Note that, I have colored the entries which are included in the formula, to calculate the Average.

I don’t want to do the Average, on the larger Positions; how do we do this.? The AVERAGE function doesn’t allow conditions. Another function, Excel provides, is AVERAGEIF; which allows applying conditions to select the data to find the Average.

AVERAGEIF function

Microsoft Excel provides another variation of the AVERAGE function; which is the AVERAGEIF function, used to exclude some data in the data set depending on some criteria.

Syntax: AVERGAEIF (range, criteria) OR
Syntax: AVERAGEIF (criteria_range, criteria, [average_range])

For example, the average of 0, 1, 2, 3, 4 is (0+1+2+3+4)/5 = 2. If we exclude 0, the Average would be 2.5.

By using the AVERAGE function, we can not omit the values. To exclude some values (for example, 0 here) from the data set, the AVERAGEIF function will be useful.

Lets’ take the above sample data; adding “Clicks” sample data to it. Clicks data represents, that the User clicks on the Web Site links; displayed in the Search Result. If your Web Site is appearing on the first page, of Search Results; you will have more Visitors (more Clicks) to your site.

I want to omit the Positions which are > 20 and want to find the Average Position in Search Results. We need to use the AVERAGEIF function, and the criteria should be, to consider the Position values which are below 20 (which means, omitting the positions which are >=20).

Observe that, from the above data; position 22.3 was omitted and only the rest of the positions were considered for the Average position. Because those values are “< 20”; satisfied the given condition.

Does it possible to get the Average Position, based on the Clicks data, instead of the Positions data.? Yes. The second Syntax, mentioned above, is useful for this. So far, we have applied the condition to the same data; from which we want to get the Average. But AVERAGEIF allows us to apply the condition on the different data ranges; to get the Average. Again, I am mentioning the second Syntax here:

Syntax: AVERAGEIF (criteria_range, criteria, [average_range])

where:

The “criteria_range” is the condition range on which you want to apply the condition;

The “criteria” is the condition to apply on the data range mentioned in “criteria_range“;

“average_range” is the range from which you want to find the Average.

Lets’ find the Average Position if the Website has any Visitors. Here the “criteria_range” is Clicks, and the condition is “>0”; that means, Website has any Visitors (or Clicks). We need to get the Average Position; so, the “average_range” is Positions. The formula & the result looks like below:

You must notice, how the data was filtered (from the above condition); based on the data in Clicks; to find the Average Position from the Positions data.

I hope the AVERAGEIF function is clear to you. It considers only one criterion or condition; to calculate the Average value. How about, if we have multiple conditions to calculate the Average.? Hence we have the AVERAGEIFS function.

I recommend you understand completely, the AVERAGE & AVERAGEIF functions, before moving to AVERAGEIFS.

AVERAGEIFS function

Microsoft Excel provides another interesting variation of the AVERAGE  function is the AVERAGEIFS function. This function allows multiple criteria or conditions to calculate the Average value. Each condition should be applied in a specified range.

Syntax: AVERAGEIFS (average_range, criteria_range1, condition1, [criteria_range2, condition2], ...)

Lets’ take the above sample data, and add the “Impressions” sample data to it. The sample data looks like below. Impressions represent how many times the Web Site appears in the Search Result; Clicks data represents the number of visitors to the Web Site.

Based on the Position in the Search Results, the Web Site Impressions, and the site Visitors may increase.

Lets’ calculate the Average Position value; based on the conditions: Should have visitors to the Web Site & at least 50 Impressions of the site. The formula looks like below:

Have you noticed the above data.? Only a few entries from Positions were satisfied with the above conditions; and I have colored them, those Positions. 

Happy reading!

I may explain more Excel functions in upcoming Articles.

I hope you liked this Article. Please give your feedback through the below Comments.

🙂 Sahida