Average formula in excel – AVERAGE function

Average is the central tendency of a given statistical data. It is the location of the center of a group of numbers in a statistical distribution. There are three most common ways to measure the average. The average formula in excel also included the following threethods.

Average- It is the arithmetical mean, which is calculated by adding numbers and dividing by the count of those numbers. For instance, the average of 1, 4, 3, 2, 7, and 7 is 24/6 equals 4.

Median- It is the middle number of a group of numbers. That means half of the numbers are greater than the median and half of the numbers are lower than the median. For instance, the median of 1, 4, 3, 2, 7, and 7 is 4.

Mode- It is the most frequently occurring number in a group of numbers.  For instance, the mode of 1, 5, 3, 2, 7, and 7 is 7.

The average formula in excel – AVERAGE function

In general, what we mean by average is the Arithmetical mean of a group of numbers. With Microsoft Excel, the AVERAGE Function is categorized under the Excel Statistical Function that returns the average value of a given group of numbers. The average function can handle up to 255 data that can include numbers, cell references, ranges, arrays, and constants.

Problem: How to average the mark sheet of students

Following are the marks of five students in four subjects Physics, Math, Chemistry, and Literature. We need to find the average marks obtained by each student.

Solution One: By using the AVERAGE function in Excel

The AVERAGE function syntax has the following arguments:

Number1 (required)- the cell reference or range that you want to calculate the average.

Number2 (Optional)- additional numbers, cell references, or ranges that you want to calculate the average, up to a maximum of 255.

Only one argument is required but as you want to calculate the average you have at least two arguments, right?

Now we will calculate the average marks of each student by using the AVERAGE function.

We can also calculate the average value by using a range of cells.

Important Facts of AVERAGE function

• You can calculate up to 255 data.
• Arguments can either be numbers or names, ranges, or cell references that contain numbers.
• If a range or cell reference argument contains text, logical values, or empty cells, is ignored.
• Range or cell reference argument containing zero s counted.
• If you include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGEA function.
• If you want to calculate the average of only the values that meet certain criteria, use the AVERAGEIF function or the AVERAGEIFS function.

Solution two: By the combination of SUM and COUNT functions in Excel

You can calculate the average marks of each student in the above table by the combination of  SUM and COUNT functions.

Let’s check how it is done.

Read More: How to sum up using Excel? SUM function explained.

To exclude zero values AVERAGEIF is used

Zero value is included since zero is a valid numeric value but if you want to exclude zero values you can use the AVERAGEIF function.

The AVERAGEIF function syntax has the following arguments:

• Range (required)- One or more cells to average, including numbers or names, arrays, or references that contain numbers.
• Criteria (required)- the criteria in the form of a number, expression, cell reference, or text that defined which cells are averaged as 0, “>0”, “speakers”, or C4.
• Average_range (Optional)- The actual group of cells to average. If not mentioned range is considered.

Now we will calculate the average marks of each student by using the AVERAGEIF function exclude zero values.

Important Facts of AVERAGEIF function

• Cells in a range that contain TRUE/FALSE are ignored.
• If a cell in the average_range is an empty cell, the functions ignore it.
• If the range contains a blank or text value, the function returns the #DIV0! error value.
• If a cell in the criteria is empty, the function treats it as a zero value.
• If no cells in the range meet the criteria, the functions return the #DIV/0! error value.

Calculate an average with criteria with AVERAGEIFS function

To calculate an average with criteria, use AVERAGEIF or AVERAGEIFS. In the example below, AVERAGEIFS is used to calculate the average score for SILVER and GOLD groups:

AVERAGEA function to exclude text

You have seen that while using the AVERAGE function text values and empty cells are excluded, zero value is couted. to get rid of this we can use the function AVERAGEA. This function considers all text values and blank cells as zero values.

How to Average of top 3

Combination of the AVERAGE and LARGE functions you can calculate the average of the top nth value. It is also included the Average formula in excel.

How to Average of bottom 3

Similarly, we can calculate the average of the bottom nth value using the combination of AVERAGE and SMALL functions together. It is also included the Average formula in excel.

How to Calculate Weighted Average

A combination of SUMPRODUCT and SUM functions can calculate the weighted average. If each Quiz exam weighs 15% and the final exam weighs 55% then you can calculate the weighted average in Excel. It is also included the Average formula in excel.

**Range D9:G9 is named as Weight.

MEDIAN is the other method of calculating the average

The arithmetical mean is widely used for the methods of calculating an average but it is not the only one. There are some other methods as well like Median. Let’s have an example.

In a cricket match, 11 nos players scored runs like 125, 24, 30, 21, 0, 69, 1, 25, 5, and 5. If you calculate the arithmetical mean the average would be 29.91. If you closely monitor the distribution 125 is the higher value and the arithmetical average is 29.91. Only for the score 125, the average increase too much.

Therefore, in some cases, to calculate an average Median function is used. In the Median method, half of the numbers are greater than the median and half of the numbers are lower than the median. The MEDIAN function excludes cells that contain text, logical values, or no value.

MODE is the third method of calculating the average

The last method of calculation that I will share with you is Mode. It is the most frequently occurring number in a group of numbers.

Dear Learners, In this context, I have described to the best of my knowledge n average formula in excel. I hope, it will help you to understand. If you have any more queries please let me know by commenting below. I will try to reply to your comments. Thank You.

Tutorial credit: Microsoft Support.