Mastering Google Sheets' powerful equation editor is key to calculating averages, a task you're likely to perform frequently in your data analysis. While the AVERAGE function seems intuitive, the nuances and use-cases might not be immediately obvious. Let's dive in and explore how to calculate averages in Google Sheets with ease and effectiveness.
Before we start, ensure your Google Sheet is up-to-date and you're using a supported browser. Now, let's learn the simple and advanced ways to make an average on Google Sheets.
Basic Averaging in Google Sheets
The most straightforward approach to finding an average in Google Sheets is using the AVERAGE function. This function calculates the average of a list of values, or the values in a range.
For instance, to find the average of numbers in cells A2 to A6, you would input the formula as follows: =AVERAGE(A2:A6). Then press Enter, and Google Sheets will display the result next to the formula.
Using the AVERAGE Function
The AVERAGE function uses the following syntax:
=AVERAGE(value1, value2, ...) |
Where 'value1', 'value2', ... are the numbers or cells containing the numbers for which you want to calculate the average. You can also use a range, like A2:A6, instead of listing the cells out.
For example, if you have the following data in cells A1 to A5:
- A1: 10
- A2: 20
- A3: 30
- A4: 40
- A5: 50
Then, to find the average of these numbers, you would use the formula =AVERAGE(A1:A5). This will return 30, the average of the numbers from A1 to A5.
Finding the Average of Sequences with Different Rows
What if you need to find the average of values in different rows but in the same column? For this, you can use a reference range in the AVERAGE function. Suppose you have the following data:
- B2: 10
- C2: 20
- D2: 30
- B3: 40
- C3: 50
To find the average of the values in the first row (B2, C2, D2) and the second row (B3, C3), you would use: =AVERAGE(B2:C2,B3:C3). This will return the average of the values in the two ranges.
Advanced Averaging Techniques
Google Sheets offers many advanced functions to calculate averages in different scenarios. Let's explore two useful ones: AVERAGEIF and AVERAGEIFS.
But before proceeding, let's set the stage with an example. Suppose you have a sheet named 'Sales' with columns for 'Year', 'Month', 'Salesperson', 'Target', and 'Actual'. You want to calculate the average actual sales for each salesperson.
AVERAGEIF Function
The AVERAGEIF function calculates the average of a range of cells, only including cells where the corresponding cells in another range meet a given criterion. The syntax is:
=AVERAGEIF(range, criterion) |
Let's say you want to find the average actual sales for the salesperson 'John Doe'. You would input the formula =AVERAGEIF(B2:B50, "John Doe", D2:D50). This formula calculates the average of the 'Actual' column (D2:D50) where the 'Salesperson' column (B2:B50) contains 'John Doe'.
AVERAGEIFS Function
You may also want to average values based on multiple conditions. In that case, use the AVERAGEIFS function, which is similar to AVERAGEIF but can accommodate multiple criteria. The syntax is:
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
Suppose you want to find the average actual sales in 2020. You can use: =AVERAGEIFS(D2:D50, A2:A50, "2020"). This calculates the average of the 'Actual' column (D2:D50) where the 'Year' column (A2:A50) is '2020'.
By learning these techniques, you'll quickly become adept at using Google Sheets to calculate averages for a wide range of scenarios.
Remember, mastering these techniques not only makes you more efficient but also enables you to analyze and present your data more effectively. Keep practicing, explore, and best of luck in your data analysis journey! Now that you know how to make an average on Google Sheets, why not try creating more detailed or complex reports?