AVERAGEIFS - Descriptive Statistics using Microsoft Excel Statistical Functions

The practice sheet can be downloaded from Link. Statistics Marks Data - Download Sheet

About the Data Sheet - The data in this sheet is related to marks scored by 100 Students in a Statistical Test. 

Based on the data, we will use Microsoft Excel Statistical functions to analyse the descriptive statistics. 

AVERAGEIFS

The AVERAGEIFS function applies multiple criteria to multiple cells across multiple ranges and counts the number of times all criteria are met.  Returns the average (arithmetic mean) of all cells that meet multiple criteria.

=AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

In the Data Sheet, we have Data from A2:A101, A1 being the header of the Data. Two criterias are given in the function below and the Two criterias are "<=" and ">=" in the cells reference in D27:D34 & E27:E34 

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D27,$A$2:$A$101,E27)

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D28,$A$2:$A$101,E28)

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D29,$A$2:$A$101,E29)

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D30,$A$2:$A$101,E30)

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D31,$A$2:$A$101,E31)

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D32,$A$2:$A$101,E32)

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D33,$A$2:$A$101,E33)

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,D34,$A$2:$A$101,E34)

or

=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=21",$A$2:$A$101,"<=30")
=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=31",$A$2:$A$101,"<=40")
=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=41",$A$2:$A$101,"<=50")
=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=51",$A$2:$A$101,"<=60")
=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=61",$A$2:$A$101,"<=70")
=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=71",$A$2:$A$101,"<=80")
=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=81",$A$2:$A$101,"<=90")
=AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">=91",$A$2:$A$101,"<=100")

The Result is below

The result gives the average of marks  of students who scored between 21 & 30, 31 & 40, 41 & 50, 51 &60, etc. To avoid double counting the class intervals are given as 21 & 30. The Equals sign is also important along with > or < signs.

The AVERAGEIFS function can also be used with Text criterias. Multiple criterias can be given in this function.

The practice sheet can be downloaded from Link. Statistics Marks Data - Download Sheet