Quick Links
The AVERAGE functions are your secret weapon for tasks like analyzing household expenses, tracking daily activities, or managing more complex datasets. Let’s show you how they work.
The AVERAGE function calculates the mean of a range of numbers by summing them up and dividing by the count of numbers. Here’s the syntax for the AVERAGE function:
Whererangerepresents the range of cells you want to calculate the average for.
AVERAGE Calculation Example
Let’s say you want to calculate the average of your monthly utility costs, and your data is in two columns. The utility names are in cellsA2:A5of columnA, while the costs are in cellsB2:B5of columnB.
To find the average monthly utility costs, use the formula:
In my case, the average monthly utility cost is $128.75.
The AVERAGEA function calculates the average of a range, considering both numbers and text. However, text is treated as zero during the calculation. This function can be useful when you want to calculate an average for a dataset that includes non-numerical content.
Here’s the syntax for the AVERAGEA function:
Whererangerepresents the range of cells you want to include in the average.
AVERAGEA Calculation Example
Imagine you’ve just learned sometask management tipsand decided to create a to-do list. However, while tracking the number of tasks completed each day, you didn’t mark some tasks as complete and typed N/A instead.
In this example, let’s assume the numbers of the tasks you completed are in cellsB2:B6. To calculate the average, use the formula:
Since I have “N/A” in cellB4, that’s treated as zero during the calculation.
The DAVERAGE function calculates the average of values in a specified column within a database, but only for the rows that meet the given criteria. The syntax for the DAVERAGE function is:
Wheredatabaserepresents the range of cells that make up the database,fieldis the column to average, andcriteriais the range containing the conditions that filter which rows to include in the average.
DAVERAGE Calculation Example
Let’s say you’re tracking the average time spent watching TV, but you only want to include days you watched for over 2 hours. The days you watched TV are in cellsA1:A5, and the hours spent are in cellsB2:B6.
Also, let’s say you have the criteria data in columnD. Since we’re focusing on the days you watched TV for more than 2 hours, you’d have to includeHoursin cellD2and>2(greater than 2) in cellD3.
Finally, to calculate the average time you spent watching TV for more than 2 hours during the week, you’d use the formula:
In my case, there were three days in the week when I watched TV for more than 2 hours. Now, the average for those days is 4 hours.
4AVERAGEIF
The AVERAGEIF function calculates the average of the values in the cells that meet a specified condition. Here’s the syntax for the AVERAGEIF function:
Whererangerepresents the cells you want to evaluate against the criteria,criteriais the condition that determines which cells to include in the average, andaverage_range(optional) is the actual range of cells to average.
AVERAGEIF Calculation Example
Let’s say you want to find the average weekly fuel expenses only for the cases where you spend more than $50. Assume you have the fuel costs in cellsB2:B6.
To get the average of the values above $50, use the formula:
In my case, the average cost for all the values above $50 is $65.
5AVERAGEIFS
The AVERAGEIFS function is similar to AVERAGEIF but allows for multiple criteria. The syntax for the AVERAGEIFS function is:
Whereaverage_rangeis the range of cells to average,criteria_range1is the rangeyou want to evaluate against the first criterion,criteria1is the first condition determiningwhich cells to include in the average, and additional ranges and criteria can be specified.
AVERAGEIFS Calculation Example
Assume you’re tracking sales data for different products in various cities. The cities are in cellsA2:A6, the products in cellsB2:B6, the number of products sold in cellsC2:C6, and the months in which the products are sold in cellsD2:D6.
Let’s say you want to find the average sales in a range where the city is New York and the product is Gaming PC. In this case, the formula would be:
This will return the average sales for New York and Gaming PC across different periods. In my case, the average is 175 gaming PCs sold in New York.
6AVERAGE.WEIGHTED
The AVERAGE.WEIGHTED function calculates a weighted average, which is useful when some values in your dataset should contribute more to the average than others. It considers both the values and their respective weights in the calculation. A common example of this iscalculating your GPA.
Here’s the syntax for the AVERAGE.WEIGHTED function:
Wherevaluesrepresents the range of cells containing the values to average, andweightsrepresents the range of cells containing the corresponding weights for the values.
AVERAGE.WEIGHTED Calculation Example
Imagine you’re a student who just learnedtips on how to have effective study sessions. Now, you want to calculate a weighted average for study hours across different subjects, where certain subjects are more important than others. The subjects are in cellsA2:A6, the study hours in cellsB2:B6, and the weights in cellsC2:C6.
The weight values are 1-4, where 1 signifies “less important” and 4 implies “most important.”
To calculate the weighted average of your study hours based on the importance of each subject, use:
This formula will multiply the study hours by their corresponding weights, sum these values, and then divide by the total weight to give you an overall weighted average study time.
In my case, the weighted average study time is 7.5 hours. This means that, when considering each subject’s importance (weight), my average study time is equivalent to 7.5 hours per subject.
Now, here’s how the weighted average can help me determine if my current study time aligns with my academic priorities.
The AVERAGE functions offer a simple yet powerful means of handling data. By using them effectively, you’re able to seamlesslyperform data analysis in Google Sheetsand draw meaningful conclusions from complex datasets.
