Steps on How to Calculate Average in Excel (With Importance)

Businesses use Excel to perform several operations and compile their financial data efficiently. The average function in Excel is helpful, as it provides a standard for businesses to estimate their performance indexes. Learning how to calculate average using Excel can help you better use your spreadsheets and process financial data. In this article, we explain the importance of knowing how to calculate average in Excel, outline steps for calculating the average value for a set of data, explore the calculation of average with specifying criteria, and provide examples to guide you.

Importance of knowing how to calculate average in Excel

Knowing how to calculate average in Excel can help you determine a standard for comparing individual data points. For example, a manager can calculate the average number of hours customer service representatives spend on a client. This can help them determine a standard for the number of customers they expect every representative to assist daily.

An average also provides a central value for a set of data. This facilitates clarity and enhances readability. You can also use an average value to observe other data. For example, if your employer wants to open a new coffee shop across from a library, you can estimate sales by calculating the average number of customers that visit the library daily.

Related: 5 Basic Excel Skills and How to Include Them in Your Resume

How to get the average value of a data set

Here are steps to follow to calculate the average value of a data set:

1. Open a file

You can create a new Excel file or open an existing file. To create a new file, click on "File" at the top left corner of the menu bar. You can scroll down to "New" to select either an Excel template or a blank workbook. If you want to open an existing spreadsheet, click on "File." Then you can select the file of the saved the document. Alternatively, you can open document on your computer and double-click on it.

Related: How to Convert Excel to Google Sheets (With Benefits and Tips)

2. Input your data into the spreadsheet

Using the average formula in Excel requires a minimum of one column or row. The number of columns or rows generally depends on your data. If you're working on a program that allows you to export a data set into an Excel spreadsheet, you can export your data set instead of manually typing each piece of information in your spreadsheet.

This is particularly useful if you're working with a large data set. It reduces the risk of typographical errors. You may consider organizing imported data and deleting irrelevant information. Ensure you enter accurate information into the spreadsheet, as Excel uses these values to calculate the average.

Related: How to Calculate Variance in Excel (With Steps and Examples)

3. Use your average formula

There are two ways of using the average formula. You can use the formula option on the top bar menu. To do this, select the cells with the data you want, and click on "Formula" from the menu. For newer versions of Excel, it opens an option from which you can select "AutoSum." Scroll down and select " Average" from the options. This provides the average value for the selected data. Alternatively, you can select an empty cell to enter the average formula within the spreadsheet. Click on this cell and types the syntax "=AVERAGE" in it.

You can highlight the data you want to work with, then press the enter key. If you want to select only some of the data set instead of all of it, you can hold the control key down and click the cells with the relevant data individually. For example, you have data for the daily sales numbers for June and you wish to calculate the average sales for the month. If the data set runs from column A rows 2 through 31, the formula for this calculation is:

=AVERAGE(A2: A31)

Related: How to Make a Bar Graph in Excel (Plus Tips and Benefits)

Calculation of average with specifying criteria

Here are ways to calculate the mean of a data set with specifying criteria:

Average of data set that includes empty cells, text, or boolean values

You can calculate the average values for data sets with empty cells or text in Excel. To do this, you can enter the data into the spreadsheet, organize it, and select an empty cell. Enter the syntax "=AVERAGEA." You can select the cells with the relevant data you want to include in your calculations. This returns the average data, including the cells with texts and empty cells. The formula is:

=AVERAGEA(array of numbers)

Average of data set with cells that meet specific criteria

If you intend to calculate the average of certain information based on one criterion, you can use the "AVERAGEIF" formula. For example, you have a sales record of 20 products in a store, and you want to find the average price of available products. You can list the items in column A, include their prices in column B, and indicate if they're sold out or available in column C. If your data occupies rows one to 20, here's a formula for the calculation:

=AVERAGEIF(C1:C20,"available",B1:B20)

Average of data using the SUMPRODUCT function

The "SUMPRODUCT" function helps calculate the weighted average in Excel. To use this formula, you can insert your data ensuring to enter the weight of each value in different columns. You can select an empty cell to write the formula. Highlight columns or rows with the data and the weight and divide by the sum of the weight. For example, if you include the weight of each item in cells C2 through C7 and the value of each weight in cells D2 through D7, your formula may be:

=SUMPRODUCT(C2:C7,D2:D7)/SUM(C2:C7)

This formula multiplies each value by the weighted percentage, totalling the sums and dividing by the values' weight.

Examples of average calculations in Excel

Here are examples to guide you in using the different average formulas in Excel:

Example of using the "AVERAGE" formula

Here's a calculation example using the "AVERAGE" formula:

A franchise manager wants to calculate the average number of sweatshirts he sold in the past seven months. He imports the sales record for each month on an Excel spreadsheet and uses the "AVERAGE" formula to get the average sales value. In columns A and B, he writes the months and the respective sales value. The data occupies rows two to eight. The spreadsheet may look like this:

The franchise manager uses the formula:

=AVERAGE(B3:B8)

He gets the average of the data set to be approximately 257.43

Example of average calculation using the "AVERAGEIF" formula

Here's an example of a calculation using the "AVERAGEIF" formula:

A fruit store owner wishes to calculate the average price of fruits remaining at the store at the day's end. He created a spreadsheet containing information on the fruit's price, stating if they were available or sold out. He writes out the fruits in column B, puts the price of fruits in column C, and indicates if the fruit is available or sold out in column D. The spreadsheet may look like this:

The store owner uses the "AVERAGEIF" formula to determine the average price of the fruits available at the store. He uses the formula below:

=AVERAGEIF(D2:D8,"available",C2:C8)

This gives an average price of $47.25.

Example using the "SUMPRODUCT" formula

Here's an example of using the "SUMPRODUCT" formula to guide you:

A coffee shop owner wishes to calculate the average price of the day's sales. He writes out the price of the teas he sold for the day and the quantity of each. To calculate the average price, he inputs the tea type in column B, writes their respective prices in column C, and indicates the sold quantity in column D. The information runs through rows two to seven. The spreadsheet may look like this:

The store manager uses the formula:

=SUMPRODUCT(C2:C7,D2:D7)/SUM(D2:D7)

With this, he gets the average price of each type of tea he sold for that day to be $37.77.

Please note that none of the companies, organizations and institutions mentioned in this article is affiliated with indeed.