In Microsoft Excel Average function is used to calculate the average value for available numbers by supplying the range in formula. Also, it is a statistical function which is useful to analyze the data and trend.
- number1 – this is first number argument that is mandatory
- [number2] – this is second number argument which is not compulsory
How to use of Average function in Microsoft Excel?
|=AVERAGE(B6:F6)||Selection-Row Range||Return average Row wise|
|=AVERAGE(B6:B12)||Selection-Column Range||Return average column wise|
|=AVERAGE(B7,D7,F7)||Cell Selection||Return average by selection of cells|
Example – Average Function
We have taken the example of Semester wise marks for every students, to make it clear refer the below image:-
1st Example: – In this example we will understand how to calculate the average marks in Subject “A” in all semesters
Follow the steps given below:-
- Enter the function in cell G6
- =AVERAGE(B6:F6), Press Enter
- The function returns the obtained average marks in Subject “A” in all semester
2nd Example: – In this example we will understand how to calculate the average marks in 1st semester in all subjects
To return the average marks in all the subject, follow the steps given below:-
- Enter the function in cell B13
- =AVERAGE(B6:B12), Press Enter
- The function returns the average marks for all of the subjects
3rd Example: – In this example we will enter the cell reference one by one for which we want calculate the average marks of “A” subject for selected semester.
Let’s say we want to return average marks for Semester 1, 2 and 5, follow the steps given below:-
- Enter the function in cell G7
- =AVERAGE(B7,D7,F7), Press Enter
- The function calculates the average marks only for selected cells
Conclusion: – We use Average function to calculate the average values by providing the cells reference, range, number value.
TIP: – To calculate an average that excludes zero values in a list of numbers, type”<>0″ in the box next to criteria.
Enter the formula =AVERAGE(IF(C6:C12<>0,C6:C12)), and Press Ctrl+Shift+Enter
AVERAGEIF | AVERAGEIFS