Mathematically speaking, the average is the central tendency referenced by a series of numbers. Today we teach you: how to calculate grand average in excel with formula. The different functions that can be used in Microsoft Excel are very useful in different areas, both work and educational.
You can apply all this in an Excel document automatically using formulas. Then, we show you how you can do it (It should be noted that before starting you must download the latest version of Office from your page, because if not many of the alternatives described here may not appear).
How to take the average in Excel with formula?
In this section we will teach how to calculate grand average in excel using simple formula. Join us and learn how simple it can be.
To start, 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 case you have text and numbers in the same cell, you must first separate first and last names with formulas in an excel sheet.
Once you have all the arguments that are part of the mean that you want to calculate, you must follow the following steps:
- Click in an empty cell and type ‘= AVERAGE’.
- Then, open parentheses and you 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 semicolon sections or specific cells.
- After choosing all cells or sections, close parentheses and press' Enter’. Immediately the calculated average will appear in the cell where you typed the formula.
You can also use other formulas instead of ‘= AVERAGE’, you just have to follow the following steps using the SUM function and a simple division.
To be able to perform an addition and later 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 choose a continuous series of cells in a row, O ‘= SUM (cell 1: cell 2) / total numbers ‘, if you want to choose rows or columns. You can choose each specific cell that you want to be part of the calculation using ',’ to separate the data.
There are also other alternatives for use the average formula, being able to insert it in different ways:
- Place yourself in an empty cell and press on ‘fx ‘ in the toolbar what do you have at the top of the screen. Aparecerá automáticamente una ventana emergente ‘Insertar función’> ‘Promedio’> ‘Argumentos de función’> ‘Aceptar’. Following that route, uses the AVERAGE function more precisely.
- Type 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, in brackets, type the range of cells you want to calculate.
What is the formula to calculate the average?
In math take out an average is calculated by adding all the numbers and dividing by the number of numbers you added. As an example, if you want to know what the grade point average is for a subject, you must do the following:
Test notes: 7, 8, 10, 6.
Number of exam notes: 4.
Average exam score: (7 + 8 + 10 + 6) / 4 = 7,75.
That is the general formula to calculate an average, despite this, Excel offers more alternatives to calculate it. This helps for the different tasks that need to be performed, below we explain their formulas.
With percentages
To start, all the items in the calculation are added to obtain the ‘total number of items’. Then, the items that you want to be part of the calculation are taken and divided by the total number of posts. An example is the following: we have the items '5', ‘6’ and '7’ resulting in the total amount as '18'. To know the average percentage we will have to divide each item by the total number of items and multiply it by 100.
This would give us, following the example, what for '5’ the average percentage is 27,78%, for '6’ would 33,33% and the one of '7’ would 38,89%. In this way, the percentage average is presented for any number of items within a calculation. You can also add items to get the total average percentage.
With condition
Excel also makes it possible to calculate the average applying conditions that make it possible to take only the items that meet this condition. The formula is 'AVERAGE’ and is used like any other function by prepending ‘=’.
As an example, in a series of items like '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 because 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. For this case, would be adding all items greater than 3 and the result is divided by the number of items that meet the condition.
Statistics
In statistics, the average is known as arithmetic average. Also known as media, It is calculated by adding all the items in a list and dividing them by the total number of items.
Now let's see how to calculate the mean, median and mode; These calculations are important for different activities.
Half
As we previously mentioned, the mean is the overall or weighted average, also called arithmetic mean, represents a fair value. The calculation of the mean is simply the same to calculate the general average in Excel, adding all the items and dividing them by the number of items. To calculate the mean, we must use the average formula as explained in the previous section.
Median
The median is the value that occupies the center position of a series of numbers. Following the example at the beginning, if the numbers were: 6, 7, 8 Y 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 median formula which is ‘(n + 1) / 2’ which gives us the value of the place where the median is found. Notably you must have an ascending or descending organization of the data to be able to find the median.
Following the lead, when applying the formula we find that the median is in the position 25,5 from the list of items. In this way the median is calculated. To calculate the median in Excel we will use the formula: ‘= MEDIUM (Celda1; cell 2) ‘ O ‘= MEDIUM (cell 1: cell 2)’. The median may or may not be equal to one of the values in the sample range.
Fashion
The mode is the number that is repeated the most times or has the highest frequency. The way to calculate the mode is by counting the number of times an element is repeated in the data list. It should be noted that in a series of data there may be more than one fashion, considering the number of repetitions of a value. You can even calculate the main mode and later the secondary ones, depending on the number of items.
To calculate the mode, we must use '= MODE (cell 1: cell 2)’ O '= MODE (cell 1; cell 2)’. Here we must make a clarification, and it is that depending on the version of Excel you have, you are going to use ‘= MODE () ‘,’ = MODO.UNO () 'O’ = MODO.VARIUS '.
In older versions can only calculate a number in a single way, while in the most updated versions, you must opt for WAY ONE, in case you want to search the number more often. Equally, MODE.VARIUS to find multiple values in case there is more than one mode.
How to calculate averages in rows or columns?
Is there a way calculate grand average in excel in rows or columns quicklyHere we show you how to do it.
With rows or columns
You simply have to choose with the pointer the row or column for which you want to calculate the average. Select row, as an example A1, and immediately At the bottom of the window you will find the following information: 'Average', 'Count’ and ‘Sum’. In a simple way, You should only see the data next to 'Average’ and ready.
No rows or columns
In the event that you do not have rows or columns with the data, you must apply any of the functions ‘AVERAGE’ and select each cell you want to be part of the calculation.
How to average in Excel without function?
Without considering the previous chapter, where an average can be taken without using a function like so, what's more you can do the calculation in the longest way. This is using the math operators in Excel.
In the cell where you want to save the average you will write ‘=’ and later you will open parentheses. From there you will select all the cells where the items are you want to use to calculate the average and will separate them with a '+'. This will make all the items add up. After selecting them all, will close the parentheses and write '/’ 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.
Despite this, To learn to perform all the processes, you have to keep looking for information, therefore as a recommendation you should look: How do you use all Excel formulas in a worksheet?
Or also, you can find how to insert formulas in text boxes ?, so i can handle the formulas in all possible ways.
