Google Sheets - Various Ways to Average
Getting averages of data in a Google spreadsheet is easier than ever. But did you know there are different ways to average which let you handle all kinds of situations? Let's start with the basic and work from there...
Method #1: Viewing the Average with the Quick Menu
If you highlight any range of cells on your spreadsheet, you can quickly get the average by clicking on the drop-down menu in the bottom right corner - it probably says Sum: by default, but clicking on it you can change it to Ave: - to get the average of the selected cells.
A very quick way to see the average, the drawback is that it is not on your spreadsheet so if you have other formulas that need to use the average it isn't a value on your spreadsheet.
Method #2: Using the Average Formula
In any empty cell on your spreadsheet, you can use the =AVERAGE formula. It is a formula that only has 1 criterion - the range of cells to use in calculating the average.
If they are not consecutive cells, you have to separate the ranges and/or cells with a comma.
This method works great if you have the data in a range, or even a couple ranges. However, it isn't very handy if your needed data for averaging is not consecutive with each other or changes frequently.
Method #3: Only Average values that meet a criteria
Let's say you only want to include values that meet a criteria in your average. Then you would use the formula =AVERAGEIF instead. This function has two pieces - first the range of cells, then the criteria to use in determining which cells to average. The calculation will only include the cells that meet the listed criterion - any cells that do not meet it are disregarded.
Example: I only want to average the cells with values less than 7
You can even make the criteria match the value in another cell. This can be especially useful when you want to change the value used for comparison.
Example: I only want to average the cells that are less than the value in cell B6.
This is much more dynamic than using the general average as when my data changes, if it updates to meet the criteria my formula will automatically recalculate - I don't have to redo my selection (and chance missing some!).
You can use any of the following six comparisons in your criterion (just note - they need to be inside quotation marks): =, <, <=, >, >=, <> (not equal to)
Method #4: Only Average values when another value meets a criteria
Perhaps you want to average values that are in one range of cells, but only if the values in another range of cells meets a criteria. You can also use the =AVERAGEIF formulas for this situation. It just adds a third part at the end with the range of values to use in averaging.
Example: I only want to average the amounts of the accounts that have been marked paid. My first range is the cells marking which have paid - my second range is the values to use if the cells in the first range have been marked paid.
And Beyond: If you need the really advanced? Check out the AVERAGEIFS function which lets you set multiple criteria to be met for a value to be averaged :-)
Method #1: Viewing the Average with the Quick Menu
If you highlight any range of cells on your spreadsheet, you can quickly get the average by clicking on the drop-down menu in the bottom right corner - it probably says Sum: by default, but clicking on it you can change it to Ave: - to get the average of the selected cells.
Method #2: Using the Average Formula
In any empty cell on your spreadsheet, you can use the =AVERAGE formula. It is a formula that only has 1 criterion - the range of cells to use in calculating the average.
This method works great if you have the data in a range, or even a couple ranges. However, it isn't very handy if your needed data for averaging is not consecutive with each other or changes frequently.
Method #3: Only Average values that meet a criteria
Let's say you only want to include values that meet a criteria in your average. Then you would use the formula =AVERAGEIF instead. This function has two pieces - first the range of cells, then the criteria to use in determining which cells to average. The calculation will only include the cells that meet the listed criterion - any cells that do not meet it are disregarded.
Example: I only want to average the cells with values less than 7
Example: I only want to average the cells that are less than the value in cell B6.
This is much more dynamic than using the general average as when my data changes, if it updates to meet the criteria my formula will automatically recalculate - I don't have to redo my selection (and chance missing some!).
You can use any of the following six comparisons in your criterion (just note - they need to be inside quotation marks): =, <, <=, >, >=, <> (not equal to)
Method #4: Only Average values when another value meets a criteria
Perhaps you want to average values that are in one range of cells, but only if the values in another range of cells meets a criteria. You can also use the =AVERAGEIF formulas for this situation. It just adds a third part at the end with the range of values to use in averaging.
Example: I only want to average the amounts of the accounts that have been marked paid. My first range is the cells marking which have paid - my second range is the values to use if the cells in the first range have been marked paid.
And Beyond: If you need the really advanced? Check out the AVERAGEIFS function which lets you set multiple criteria to be met for a value to be averaged :-)
