Microsoft Excel will always be the best option for spreadsheets to make tables, records, forms and lists, with which you can organize data, calculate accounts and more. Excel can be downloaded from its official page .
Today we will show you one of the basic and most useful formulas of this program, with which you can take the average of a series of data, taking into account certain parameters if applicable. Stay to find out how to do it.
Index( )
- What is the way to get the average of your Excel file?
- If you want to get the grade point average
- The ‘AVERAGE.IF’ function
- To use the ‘AVERAGE.IF.SET’ function
- What can you do if you have multiple averages within Excel?
- Find the maximum, minimum and mode
What is the way to get the average of your Excel file?
As we all know, the average is the result given by the sum of a series of data and then dividing it by the total number of them. Although it is not a very complicated mathematical operation, the truth is that when we have a large registry, it can become tedious, but with Microsoft Excel, this is much easier.
If you want to get the grade point average
The first method is what we consider the “manual way”, imagine that you have a list of students and next to them, their grades in terms of the work and exams carried out. If all activities have the same weight, the average can be easily calculated :
- Hover over any cell you want and start by typing the equal sign “=”.
- Then open parentheses and select the cell with the first note, write the plus sign “+”, select the second cell with the second data, “+” and so on, when you finish, close parentheses.
- Then you write the division sign “/” and enter the number of data that you add to divide. Finally, press “Enter” and you will have the average of what you selected.
It is also possible to delete rows and columns from Excel in case you have information that you will no longer need to have a better order in your spreadsheet . Next, we have a more automated method using the typical program formulas, in this case =AVERAGE.
- Imagine the same case as above and hover over any empty cell, but now type =AVERAGE and open parentheses.
- Then click on the first cell and with the button pressed without releasing it, drag all the data that you are going to average and then release the click.
- Close parentheses and press “Enter”. This will calculate the average of the selected cells, instead of typing them individually.
The ‘AVERAGE.IF’ function
This is a variation of the previous formula, except that this is used to take into account a criterion, for example, continuing with the case of the previous students, you want to get the average grades but only for the girls. For this, =AVERAGE.IF is used and in it the range, the criterion and the range of the average are written, just in this order.
To use it, it is necessary that in the list, all the students have identified if it is feminine or masculine, in addition to that, you have to have written in another cell the criterion that you will use, in this case “Feminine”.
- In an empty cell, type the formula =AVERAGEIF and open parentheses.
- The first data that you are going to provide is the range of the criterion, that is, select all those cells that have the qualifier of the sex of the students.
- You write a semicolon, then you have to select the criterion itself, in this case, select the cell where you have written this condition, “Female” for example.
- Likewise, semicolon, and now it’s time to select the notes of all the students normally.
- Finally, we close parentheses and press “Enter”, we will have the average, but only for the girls. When you have all the calculated data and what is necessary, you can print the tables and spreadsheets in Excel , to have them at hand.
To use the ‘AVERAGE.IF.SET’ function
It is another variation and in this case, several criteria are taken into account instead of one. Again with the list of students, but there is one more classification, all the students on said list are divided into three different sections “A”, “B” and “C”; now you want to know the average of the girls, but from section “B”.
The formula to write is =AVERAGE.IF.SET and the data to enter are the average range, range of criterion 1 and criterion 1, range of criterion 2 and criterion 2 and so on. As in the previous case, in addition to identifying each student’s gender, each one must have the section to which they belong and in two separate boxes, have criterion 1 (Female in this case) and criterion 2 (B).
- In any cell, type the formula =AVERAGE.IF.SET and open parentheses.
- The first data is the range of what you are going to average, then select the cells that have the grades.
- Then select the range of criterion 1, which is the qualifying gender of all students.
- Select the cell in which the first criterion (Female) is found.
- Now it is time to repeat steps 3 and 4, but referring to the condition of the section to which they belong.
- Close parentheses and press “Enter”.
What can you do if you have multiple averages within Excel?
There are other formulas to better organize the data, instead of hiding columns in spreadsheets that are the minimum, the maximum and the mode. This organization will be able to serve you in a better way to order the averaged data of your registry.
Find the maximum, minimum and mode
To use them, write =MAX, =MIN or =MODE in a separate cell, as the case may be, and open parentheses, select the data range , close parentheses and press “Enter”. The maximum will give you the highest value, the minimum the lowest value, and the mode will give you the value that occurs the most.