Calculate Average in Excel


Home >> How to Use Excel Tutorial >> Excel Calculations >> Calculate Average in Excel



In this tutorial, you will learn how to calculate average in excel. This calculation process can be done using two methods, manually or by utilizing excel average formulas.


Why do We Need to Learn About Calculate Average Excel?


In the data processing of a spreadsheet file, sometimes we need to get the mean value of numbers. This calculation process is needed especially when we often process quantitative data. Usually, it is used when we want to have a comparison value to assess the good or bad performance of entities. Or probably when we want to find the general picture of our numeric data spread. If we don’t understand the formula to calculate average in excel, then those processes will be troublesome to do.

As an example of this need at work, let’s say we are processing sales data of all company’s products. To know product sales are good or bad compared to others, we should know the sales mean of all products. Its value needed to be found if we want to analyze data well. For this analysis process, we must know how to find average in excel sheet.

As per this need, there are ways to calculate average in excel that you can implement. By understanding those ways, it can be easier for you to process your numbers. Your data analysis will become faster to be done.


What is Calculate Average in Excel?


Calculate average in excel is a function to divide the sum of numbers with the amount of them. There are some ways usually used to do this calculation. Those are the manual method and using AVERAGE, AVERAGEIF, and AVERAGEIFS formulas.


How to Calculate Mean in Excel?


The following will explain how to calculate average in excel manually and using the average formula in excel. The formulas that can be used are AVERAGE, AVERAGEIF, and AVERAGEIFS.

To calculate average in excel manually, first, we need to know the sum and amount of numbers that we have. A more practical way is probably by using excel mean formula which is AVERAGE. There are also more complex formulas like AVERAGEIF and AVERAGEIFS for us to learn in this tutorial. They are usually used if the calculation result needs to be gained from numbers with specific criteria.

Actually, there is also another formula which is AVERAGEA. However, it is seldomly used in our data processing for a spreadsheet file. Its difference with AVERAGE is also little. The only difference is AVERAGEA considers the text and TRUE/FALSE condition in its calculation. It is rarely needed in our data processing.


Method 1: Manual Method ( / Sign)

  1. Type equal sign ( = ) in the cell where you want to put the calculation result

    Calculate Average in Excel - Screenshot of Step 1-1

  2. Input the sum of numbers or cell coordinate where you put the sum of numbers to be processed after =. Here, you can also use excel sum formulas if your numbers haven’t been summed. Type slash sign ( / ) afterwards

    Calculate Average in Excel - Screenshot of Step 1-2

  3. Input the amount of numbers or cell coordinate where you put the amount of numbers. You can use the COUNT formula if you haven’t known the amount of your numbers

    Calculate Average in Excel - Screenshot of Step 1-3

  4. Press Enter
  5. The process is done!

    Calculate Average in Excel - Screenshot of Step 1-5




Method 2: AVERAGE

  1. Type equal sign ( = ) in the cell where you want to put the calculation result

    Calculate Average in Excel - Screenshot of Step 2-1

  2. Type AVERAGE (can be with large and small letters) and open bracket sign after =

    Calculate Average in Excel - Screenshot of Step 2-2

  3. Type the numbers or cell coordinates where you put the numbers to be processed after open bracket sign. Input comma signs ( , ) between the numbers or cell coordinates. Drag your cell cursor from the top left to the bottom right if the number cells are near each othe

    Calculate Average in Excel - Screenshot of Step 2-3

  4. Type close bracket sign after all the numbers to be processed have been inputted

    Calculate Average in Excel - Screenshot of Step 2-4

  5. Press Enter
  6. The process is done!

    Calculate Average in Excel - Screenshot of Step 2-6




Method 3: AVERAGEIF

AVERAGEIF can be used if you have a specific criterion for the numbers that you want to process. It is usually useful if you have a table with many numbers and you want to average some numbers only. The way to calculate average in excel using AVERAGEIF (and AVERAGEIFS) becomes important to be understood because of its unique function.

  1. Type equal sign ( = ) in the cell where you want to put the calculation result

    Calculate Average in Excel - Screenshot of Step 3-1

  2. Type AVERAGEIF (can be with large and small letters) and open bracket sign after =

    Calculate Average in Excel - Screenshot of Step 3-2

  3. Drag cursor on the row/column cells where you want to evaluate using your criterion then type a comma sign ( , )

    Calculate Average in Excel - Screenshot of Step 3-3

  4. Input your criterion then type a comma sign. The criterion must be inside quote signs ( “” ) if the criterion is for text. You need to also use the quote signs if the criterion is more than/less than criterion for numbers

    Calculate Average in Excel - Screenshot of Step 3-4

  5. Drag cursor on all cells where the numbers you want to process based on the criterion are in. The digits cells must be inline vertically/horizontally with the cells where you evaluate with your criterion. They should also have the same number of cells

    Calculate Average in Excel - Screenshot of Step 3-5

  6. Type close bracket sign after all the numbers or cell coordinates have already been inputted

    Calculate Average in Excel - Screenshot of Step 3-6

  7. Type Enter
  8. The process is done!

    Calculate Average in Excel - Screenshot of Step 3-8




Method 4: AVERAGEIFS

AVERAGEIFS has almost the same function as AVERAGEIF. It is just you have to use AVERAGEIFS if your criteria are more than one.

  1. Type equal sign ( = ) in the cell where you want to put the calculation result

    Calculate Average in Excel - Screenshot of Step 4-1

  2. Type AVERAGEIFS (can be with large and small letters) and open bracket sign after =

    Calculate Average in Excel - Screenshot of Step 4-2

  3. Drag cursor on the row/column cells where the numbers you want to process based on your criteria are in. Then input a comma sign ( , )

    Calculate Average in Excel - Screenshot of Step 4-3

  4. Drag cursor on the row/column cells where you want to evaluate with your first criterion then types a comma sign. These cells must be inline vertically/horizontally with the numbers cells earlier. Make sure also that both of them have the same amount of cells

    Calculate Average in Excel - Screenshot of Step 4-4

  5. Input your first criterion. The criterion must be inside quote signs ( “” ) if the criterion is for text. You need to also use the quote signs if the criterion is more than/less than criterion for numbers

    Calculate Average in Excel - Screenshot of Step 4-5

  6. Type a comma sign after the first criterion is inputted if there are still other criteria to be inputted. Redo steps 4-5 if that is the case

    Calculate Average in Excel - Screenshot of Step 4-6

  7. Type close bracket sign after all the criteria have been inputted.

    Calculate Average in Excel - Screenshot of Step 4-7

  8. Type Enter
  9. The process is done!

    Calculate Average in Excel - Screenshot of Step 4-9



Exercise


After you have learned how to calculate average in excel using the four methods above, now let’s do an exercise. The exercise should be done so you can practice your understanding of how to calculate average in excel better!

Download the file from the following link and answer all the questions. Please download the answer key file if you have done the exercise and sure about your answers!

Link of the exercise on how to calculate average in excel:
Download here

Questions

  1. What is the middle number of students for all classes?
  2. What is the middle number of students in class C? Use AVERAGEIF to answer!
  3. What is the middle number of students in class 1 and 3 with code D? Use AVERAGEIFS to answer! (Hint: for “not the same as” criterion, use <> sign)

Link of the answer key file for the exercise on how to calculate average in excel:
Download here


Additional Notes


After you have done the calculation, maybe you want to label each data entry based on the comparison with the mean. For that, you need to use the IF formula.



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert