Mathematically speaking, the average is the central tendency referenced by a series of numbers. Today we will tell you: how to calculate the general average in Excel with a formula. The different functions that can be used in Microsoft Excel are very useful in different areas, both work and educational.
How to Calculate or Take the General Average in Excel with Formula – Step by Step
You can apply all this in an Excel document automatically using formulas . Next, we will tell you how you can do it (it should be noted that before starting you should download the latest version of Office from its page , because if not, many options described here will not appear).
- How to take the average in Excel with formula?
- What is the formula to calculate the average?
- With percentages
- With condition
- How to calculate the mean, median and mode?
- How to calculate averages in rows or columns?
- With rows or columns
- No rows or columns
- How to average in Excel without function?
How to take the average in Excel with formula?
In this section we will teach how to calculate the general average in Excel using a simple formula. Join us and learn how simple it can be.
To begin, we will need a series of different numbers to be able to calculate the average of them and add them to all of them. In the event that you have text and numbers in the same cell, you must first separate names and surnames with formulas in an Excel sheet .
Once you have all the arguments that are part of the average that you want to calculate, you will have to follow the following steps:
- Click on an empty cell and type ‘= AVERAGE’.
- Next, you open parentheses and select all the cells that will be part of the average calculation. Remember that if you enter the colon you will be indicating a section, and with the semicolon sections or specific cells.
- After you select all cells or sections, close parentheses and press ‘Enter’. Immediately the calculated average will appear in the cell where you wrote the formula.
You can also use other formulas instead of ‘= AVERAGE’, you just have to follow the next steps using the SUM function and a simple division .
To be able to perform an addition and then a division automatically, you must use the formula: ‘ = SUM (Cell 1, Cell 2, Cell 3, Cell 4, …) / number of numbers you selected’ . In case you want to select a continuous series of cells in a row, or ‘ = SUM (Cell 1: Cell 2) / total numbers’ , if you want to select rows or columns. You can select each specific cell that you want to be part of the calculation by using ‘,’ to separate the data.
There are also other ways to use the average formula , being able to insert it in different ways:
- Position yourself on an empty cell and click on ‘ fx’in the toolbar at the top of the screen. A pop-up window ‘Insert Function’> ‘Average’> ‘Function Arguments’> ‘OK’ will automatically appear. Following that route you use the AVERAGE function more precisely.
- Write the = sign in an empty cell. At the top of the screen, from where you can edit everything you write in a cell, a drop-down menu will open with all the functions that Excel has. Now between the parentheses write the range of cells you want to calculate.
What is the formula to calculate the average?
In math, getting an average is calculated by adding all the numbers and dividing by the number of numbers you added . So, for example, if you want to know how much is the grade point average of a subject you should do the following:
Exam marks: 7, 8, 10, 6.
Number of exam marks: 4.
Average exam marks: (7 + 8 + 10 + 6) / 4 = 7.75.
That’s the general formula for calculating an average, however, Excel offers more ways to calculate it . This helps for different tasks that must be carried out, below we explain their formulas.
To begin with, all the elements of the calculation are added together to obtain ‘total number of elements’. Next, the elements that you want to be part of the calculation are taken and divided by the total number of elements . An example is the following: we have the elements ‘5’, ‘6’ and ‘7’ resulting in the total amount as ’18’. To know the percentage average we will have to divide each element by the total number of elements and multiply it by 100.
This would give us, following the example, that for ‘5’ the percentage average is 27.78%, for ‘6’ it would be 33.33% and that of ‘7’ would be 38.89%. In this way, the percentage average is presented for any number of elements within a calculation. Items can also be added together to get the total percentage average .
Excel also allows you to calculate the average by applying conditions that allow only the elements that meet this condition to be taken. The formula is ‘AVERAGEI’ and is used like any other function by prepending ‘=’.
For example, in a series of elements such as ‘5, 4, 5, 4, 3, 5, 6, 3’ where you want to calculate the average of all numbers greater than 5, the result would be 6. This is due to that only one value meets the condition , resulting in an average equal to the element. On the other hand, with a condition ‘numbers greater than 3’ the result of the average would be 4.83. In this case, it would be adding all the elements greater than 3 and the result is divided by the number of elements that meet the condition.
In statistics, the average is known as the arithmetic mean . Also known as the mean, it is calculated by adding all the items in a list and dividing them by the total number of items.
How to calculate the mean, median and mode?
Now let’s see how to calculate the mean, median, and mode ; These calculations are important for different activities.
As we mentioned earlier, the mean is the general or weighted average , also called the arithmetic mean, it represents a fair value. The calculation of the mean basically the same to calculate the general average in Excel, adding all the elements and dividing it by the number of elements . To calculate the mean, we must use the average formula as explained in the previous section.
The median is the value that occupies the central position of a series of numbers. Following the example at the beginning, if the numbers were: 6, 7, 8 and 10, the median would be 7.5. To better understand this calculation we will do another example. Suppose you have 50 items in your data list. There is the formula for the median which is ‘(n + 1) / 2’ which gives us the value of the place where the median is. It should be noted that you must have an ascending or descending organization of the data to be able to find the median.
Following the example, when applying the formula we find that the median is in position 25.5 of the list of elements. That way the median is calculated. To calculate the median in Excel we are going to use the formula: ‘ = MEDIAN (Cell1; cell 2)’ or ‘= MEDIAN (Cell 1: Cell 2)’ . The median may or may not be equal to one of the values in the sample range.
The mode is the number that is repeated the most times or has the most frequency . The way to calculate the mode is by counting the number of times an item is repeated in the data list. It should be noted that in a data series there may be more than one mode , considering the number of repetitions of a value. You can even calculate the main mode and then the secondary ones, depending on the number of elements.
To calculate the mode, we must use ‘= MODE (Cell 1: Cell 2)’ or ‘= MODE (Cell 1; Cell 2)’ . Here we must make a clarification, and it is that depending on the version of Excel you have, you will use ‘ = MODE ()’, ‘= MODE.ONE ()’ or ‘= MODE.VARIUS’.
In older versions you can only calculate a single fashion number , while in more updated versions, you should opt for MODE.ONE , in case you want to find the number more frequently. Likewise, MODE.VARIUS to search for several values in the event that there is more than one mode.
How to calculate averages in rows or columns?
There is a way to calculate general average in Excel in rows or columns quickly , here we explain how to do it.
With rows or columns
You simply have to select with the pointer the row or column for which you want to calculate the average. Select the row, for example A1, and immediately in the lower part of the window you will find the following data: ‘Average’, ‘Count’ and ‘Sum’ . In a simple way, you just have to see the data next to ‘Average’ and that’s it.
No rows or columns
In the event that you do not have rows or columns with the data, you will have to apply one of the ‘AVERAGE’ functions and select each cell that you want to be part of the calculation.
How to average in Excel without function?
Without considering the previous section, where you can take an average without using a function as such, you can also do the calculation in the longest way . This is using the mathematical operators in Excel.
In the cell where you want to save the average you will write ‘=’ and then you will open parentheses. From there you will select all the cells where the elements you want to use to calculate the average are located and you will separate them with a ‘+’. This will cause all the elements to be added together. After selecting all of them, you will close the parentheses and type ‘/’ followed by the number of items you selected.
As you can see, using Excel formulas is extremely simple and practical , especially when it comes to long lists.
However, to perfectly learn how to carry out all the processes, you have to keep looking for information, so as a recommendation you should look : How are all the Excel formulas used in a spreadsheet ?
Or also, you can search how to insert formulas in text boxes ?, so that you can handle the formulas in all possible ways .