AVERAGEIF - 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. 

AVERAGEIF

This function returns the average value (arithmetic mean) of all the cells in a range that meet given specific criteria. Only One criteria can be specified.

=AVERAGEIF(range,criteria,average_range)

Range of the Data from which the Criteria needs to be compared with. Range in the Statistics Marks Data Sheet will be A2:A101.

Criteria can be specified as number like 20 or ">20" or Cell where we put the criteria like B4. In B4 cell we can give number 20 or >20. We can also specify the Text like Names, etc. If criteria other than Cell Numbers, criteria should be in double quotes. 

When Text is used it has to be typed in Double Quotes in Function or Text criteria can be given a specific Cell. 

In the Data Sheet, we have Data from A2:A101, A1 being the header of the Data.

=AVERAGEIF($A$2:$A$101,”>90”)

=AVERAGEIF($A$2:$A$101,”>80”)

=AVERAGEIF($A$2:$A$101,”>70”)

=AVERAGEIF($A$2:$A$101,”>60”)

=AVERAGEIF($A$2:$A$101,”>50”)

=AVERAGEIF($A$2:$A$101,”>40”)

=AVERAGEIF($A$2:$A$101,”>30”)

=AVERAGEIF($A$2:$A$101,”>20”)

The Result is given in the table below.

Average of all the students who scored above 90 marks is 96.55. This does not include students who exactly scored 90 Marks. For including the students who scored exactly 90 marks also we have to use ">=90" in place of ">90". Same for all the criteria.

The Dollar Sign is put as Absolute Reference and this can be done by clicking "F4" on Keyboard.

Absolute Cell reference has to be fixed else the Data result will be wrong.

Alternatively the Criteria can be given in a cell & Cell No can be given in the function.

The Criteria are given in the Cells D5:D12. Double Quotes not needed. 

In the Cells D5:D12, Criteria included are >20,>30,>40,>50,>60,>70,>80,>90 .

=AVERAGEIF($A$2:$A$101,D5)

=AVERAGEIF($A$2:$A$101,D6)

=AVERAGEIF($A$2:$A$101,D7)

=AVERAGEIF($A$2:$A$101,D8)

=AVERAGEIF($A$2:$A$101,D9)

=AVERAGEIF($A$2:$A$101,D10)

=AVERAGEIF($A$2:$A$101,D11)

=AVERAGEIF($A$2:$A$101,D12)

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