# How to master numerical data in Google Sheets with the AVERAGE function

The following article will show you how they work.

## 1. AVERAGE function

The AVERAGE function calculates the average value of a range of numbers by adding them up and dividing by the number of numbers. The following is the syntax of the AVERAGE function:

`=AVERAGE(range)`

Where **range** represents the range of cells for which you want to calculate the average.

### Example of AVERAGE calculation

Suppose you want to calculate the average value of monthly utility costs and the data is in two columns. The utility name is in cells **A2:A5** of column A, while the cost is in cells **B2:B5** of column B.

To find your average monthly utility costs, use the formula:

`=AVERAGE(B2:B5)`

In the case example, the average monthly utility cost is $128.75.

## 2. AVERAGEA function

The AVERAGEA function calculates the average value of a range, considering both numbers and text. However, the text is considered zero during the calculation. This function can be useful when you want to calculate the mean for a data set that includes non-numeric content.

The following is the syntax for the AVERAGEA function:

`=AVERAGEA(range)`

Where **range** represents the range of cells you want to include in the average.

### Example of AVERAGEA calculation

Imagine you just learned some task management tips and decided to create a to-do list. However, when tracking the number of tasks completed each day, you did not mark some tasks as completed and instead entered N/A.

In this example, let's assume the number of tasks you completed are in cells **B2:B6** . To calculate the average, use the formula:

`=AVERAGEA(B2:B6)`

Because the example has "N/A" in cell B4, this value is considered zero during the calculation.

## 3. DAVERAGE function

The DAVERAGE function calculates the average of the values in a specified column in the database, but only for those rows that meet the given criteria. The syntax of the DAVERAGE function is:

`=DAVERAGE(database, field, criteria)`

Where **database** represents the range of cells that make up the database, **field** is the column to calculate the average value and **criteria** is the range containing the filtering conditions for which rows will include the average value.

### Example of DAVERAGE calculation

Let's say you're tracking the average time spent watching TV, but you only want to include days where you watch more than 2 hours. The days you watched TV are in cells **A1:A5** and the hours spent watching TV are in cells **B2:B6.**

Additionally, let's say you have criteria data in column D. Since we're focusing on days when you watched TV for more than 2 hours, you would have to put **Hours** in cell **D2** and **>2** (greater than 2) in cell **D3** .

Finally, to calculate the average time you spend watching TV over 2 hours during the week, you would use the formula:

`=DAVERAGE(A1:B6, "Hours", D2:D3)`

In the example case, there are 3 days a week of watching TV for more than 2 hours, an average of 4 hours/day.

## 4. AVERAGEIF function

The AVERAGEIF function calculates the average of the values in cells that meet a specified condition. The following is the syntax for the AVERAGEIF function:

`=AVERAGEIF(range, criteria, [average_range])`

Where **range** represents the cells you want to evaluate against the criteria, **criteria** is the condition that determines which cells will be included in the average, and **average_range** (optional) is the actual range of cells to calculate the average .

### Example of the AVERAGEIF calculation

Let's say you want to find the average weekly fuel costs only in cases where you spend more than $50. Suppose you have fuel costs in cells **B2:B6** .

To get the average of the values above $50, use the formula:

`=AVERAGEIF(B2:B6, ">50")`

In the example case, the average cost for all values above 50 USD is 65 USD.

## 5. AVERAGEIFS function

The AVERAGEIFS function is similar to AVERAGEIF but allows multiple criteria. The syntax of the AVERAGEIFS function is:

`=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)`

Where **average_range** is the range of cells to calculate the average, **criteria_range1** is the range you want to evaluate according to the first criterion, **criteria1** is the first condition that determines which cells will be included in the average value and can be further specified. other scopes and criteria.

### AVERAGEIFS calculation example

Let's say you're tracking sales data for products in many different cities. The cities are in cells **A2:A6** , the products are in cells **B2:B6** , the number of products sold is in cells **C2:C6** , and the months in which the products were sold are in cells **D2:D6** .

Let's say you want to find average sales within the city range of New York and the product is Gaming PC. In this case, the formula will be:

`=AVERAGEIFS(C2:C6, A2:A6, "New York", B2:B6, "Gaming PC")`

This function will return the average sales for New York and Gaming PC over different periods. In the case example, the average value is 175 gaming computers sold in New York.

## 6. AVERAGE.WEIGHTED function

The AVERAGE.WEIGHTED function calculates a weighted average, useful when some values in a data set contribute more to the mean than others. This function considers both the values and their respective weights in the calculation. A common example of this is calculating GPA.

The following is the syntax of the AVERAGE.WEIGHTED function:

`=AVERAGE.WEIGHTED(values, weights)`

Where **values** denotes the range of cells containing the values to calculate the average, and **weights** denotes the range of cells containing the corresponding weights for the values.

### Example calculation of AVERAGE.WEIGHTED

Imagine you are a student who has just learned tips on how to study effectively. Now, you want to calculate a weighted average of the study hours of different subjects, with some subjects being more important than others. Subjects are in cells **A2:A6** , class hours are in cells **B2:B6** , and weights are in cells **C2:C6.**

The weight values are 1-4, where 1 represents "less important" and 4 represents "most important."

To calculate a weighted average of study hours based on the importance of each subject, use:

`=AVERAGE.WEIGHTED(B2:B6, C2:C6)`

This formula multiplies the study hours by their respective weights, adds these values, then divides by the total weight to get the overall weighted average study time.

In the example case, the weighted average study time is 7.5 hours. This means that, when considering the importance (weight) of each subject, the average study time is equivalent to 7.5 hours per subject.

Here's how AVERAGE.WEIGHTED can help determine whether your current study schedule fits your study priorities.

**Math**: 10 hours (above average of 7.5 hours). This means spending more time than average, in accordance with the priority/weight of the subject.**Science**: 8 hours (higher than 7.5 hours). This is slightly higher than the weighted average and is consistent with the importance of the subject.**History**: 5 hours (less than 7.5 hours). This is less than average, which is acceptable because this subject has a lower priority/weight.**Literature**: 4 hours (much less than 7.5 hours). This is significantly less than the average, consistent with the subject's lower priority.**Language**: 6 hours (less than 7.5 hours). This number is a bit lower but still quite close to the weighted average, which should be fine based on the subject's weight.

AVERAGE functions provide a simple yet powerful means of processing data. By using them effectively, you can perform data analysis in Google Sheets seamlessly and draw meaningful conclusions from complex data sets.

## You should read it

- How to use the AVERAGE function in Google Sheets
- How to use the AND and OR functions in Google Sheets
- How to use the SMALL function in Google Sheets
- 30+ useful Google Sheets functions
- 9 basic Google Sheets functions you should know
- How to use the UNIQUE function in Google Sheets
- How to use the MEDIAN function in Google Sheets
- How to count words on Google Sheets
- How to use the FLOOR function in Google Sheets
- Tricks using Google Sheets should not be ignored
- Keep track of the stock market with Google Sheets
- How to generate random numbers in Google Sheets

## Maybe you are interested

### Microsoft: Personnel in the AI field have salaries much higher than average

### Why is PC Copilot+ such a disaster for the average user today?

### What is the average lifespan of a laptop?

### Logitech G502 review: The best Logitech gaming mouse for the average budget

### What is the average typing speed?

### How to calculate grade point average in Excel fast and standard