How to Use Average Function in MS Excel

 

Average is meant in arithmetic to get the central number or digit of a group of numbers. Suppose we have 120 students of all classes depositing fees according to their class which becomes equal to 259600. We shall divide to total amount of fees with total numbers of students to get central figure for getting central or an average amount. But, Microsoft Excel facilitates us to get average instead of manual dividing amount. This function is called as average function.

How to use AVERAGE Function in Microsoft Excel

Follow following method to get average of fees amount

Place cursor at ending cell of fees amount and insert formula as “=AVERAGE(H2:H121)”. Other method is to get average amount is to click on dropdown button of AutoSum icon placed on Home Tab , where we can see Average function. We can also get average on clicking of AVERAGE formula in AutoSum icon.

How to use AVERAGEA Function in Microsoft Excel

It is highlighted that AVERAGE Function also includes blank Cells in the range. For instance we have not written amount infront of any student. But AVERAGE Function will give the result of 120 students. But Microsoft Excel has given another function to exclude blanks cells in the range. This means that the average amount will only be calculated for those cells in the range which have been given the prescribed amounts. This function is called AVERAGEA Function in Microsoft Excel.

To Apply AVERAGEA Function in Microsoft Excel just place cursor at the end of fees amount and type formula “=AVERAGEA(H2:H121)”. This method will give us desired results.

AVERAGE and AVERAGEA Functions give us facility to calculate overall average fees of students. But we have also to get average of every class in the school. Suppose, if we want to get average only 6th class. We will calculate manually fees amount and numbers of students in the class. But, we can calculate average amount of 6th class through other function i.e AVERAGEIF Function.

How to Use AVERAGEIF Function in Microsoft Excel

Place cursor in any cell in students sheet and type following formula to get average fees amount of 6th class.

=AVERAGEIF(F2:F121,"6th",H2:H121)

Definition of Formula applied in AVERAGEIF Function

=AVERAGEIF is the function through which we can get conditional average of data. (F2:F121, is the range of students class columns given to formula to pick only students of prescribed class. “6th” is the criteria given to formula. H2:H121 is the range of fees amount columns which means that we have asked to get average of these digits.

AVERAGEIFS Function in Microsoft Excel

AVERAGEIFS Function is used to get average of data having multiple conditions. Suppose we have to get average amount of 6th class studying in Section A or B, then we will apply AVERAGEIFS in Microsoft Excel.

How to Use AVERAGEIFS Function in Microsoft Excel

Place cursor in any cell of students’ sheet and type following formula to get average amount of students of 6th class studying in Section A.

=AVERAGEIFS (H2:H121,F2:F121,"6th",G2:G121,"A")

Definition of Formula

=AVERAGEIFS is function of Microsoft Excel through which we can get average having multiple conditions.

(H2:H121 is the range of fees amount which have been given to formula for getting average amount. F2:F121 is the range of students class columns. “6th” is first criteria given to formula that means only to get students of 6th class given in range F2:F121. G2:G121 is 2nd range of students sections columns. “A” is second criteria has been given to formula that means to acquire students of Section A in range G2:G12.