How to Calculate Average of Multiple Columns in Excel (6 Methods)

In this tutorial, I will show some methods for calculating the average of multiple columns in Excel. Usually, you can get an average of a range of having multiple columns using the AVERAGE function. However, if you want to have an average from a certain number of columns, there are ways available in Excel. I will use several Excel functions and their combinations to get the average from multiple columns.

How to Calculate Average of Multiple Columns in Excel: 6 Methods

1. Calculate Average of Multiple Columns Using AVERAGE Function

Firstly, I will use the AVERAGE function to calculate the average of some adjacent ranges located in different columns. For example, I have a dataset (B4:E11) with several students’ test scores. Now, I will calculate the average of multiple ranges from multiple columns.

Steps:

• Type the below formula in cell B13 to calculate the average of ranges B5:B10, C5:D9, and E6:E11.

• Hit Enter and you will get the average of the specified ranges of columns B, C, D, and E.

Read More: How to Average a Column in Excel

2. Define a Name to Multiple Columns and Then Get the Average

Sometimes, selecting multiple ranges from multiple columns may seem tiresome and may result in wrong results if the ranges are not selected correctly. Luckily, you can name the specified range in Excel and then pass the range as the argument of the AVERAGE function.

Steps:

• First, select the expected ranges from multiple columns by pressing the Ctrl key.
• Then go to the Name Box, give a name, and press Enter. I have named the below ranges as MultiCol.

• Now type the below formula in cell B13 and hit Enter.

• Here is the ultimate average you will get.

Read More: How to Calculate Average of Multiple Ranges in Excel 

3. Excel AVERAGEIF Function to Calculate Average of Multiple Columns

Now, I will use the AVERAGEIF function to get the average of multiple columns. In the following discussion, I will show you two examples of this function to calculate the average.

3.1. Get Average of Cells that Match a Criteria Exactly

Suppose, I have a dataset (B4:C12) containing several fruit names and their qualities in columns B and C. I will look for particular fruit names (here, Apple) in column B and calculate their average from column C.

Steps:

• Type the following formula in cell C14 and hit Enter.

• I will get the average of the quantities of all apples on this dataset.

3.2. Calculate Average of Cells that Match Criteria in a String

Previously, I calculated the average for a fruit name that was an exact match. Now, I will search for a string that matches the cell contents and then calculate the average from another column. For instance, I have a fruit name containing the string Apple as part of the cell content (e.g. Wood Apple, Pineapple, etc.). Let’s find a match for the string Apple in column B and then get the corresponding average from column C.

Steps:

• Type the below formula in cell C14.

• Press Enter and get the below result.

4. Combination of AVERAGEIF and SUMIF Functions to Get an Average of Multiple Columns

You can use a combination of Excel functions such as the AVERAGEIF and SUMIF functions to find the average from multiple columns. For example, I have a dataset (B4:E10) containing some grocery items and their unit prices and sold quantities by dates. Now, I will calculate the total price for these items from columns B, C, and E using the SUMIF and AVERAGEIF functions.

Steps:

• Type the below formula in cell E13 and hit Enter.

• We will get the below result. Use the Fill Handle (+) tool to copy the formula to the rest of the cells.

• Finally, we will get the total price for all the items as below.

🔎 How Does the Formula Work?

➤ AVERAGEIF($B$5:$B$10,B13,$E$5:$E$10)
This part of the formula returns the Unit Price of the cell content of cell B13 (Corn Flakes) which is: {5}

➤ SUMIF($B$5:$B$10,B13,$C$5:$C$10)
Now, this part of the formula returns the sold Quantity of Corn Flakes which is: {88}

➤ AVERAGEIF($B$5:$B$10,B13,$E$5:$E$10)*SUMIF($B$5:$B$10,B13,$C$5:$C$10)
Finally, the above formula multiplies 5 by 88 and returns: {440}

5. Combination of Excel AVERAGE and LARGE Functions to Get Average from Multiple Columns

You can combine the LARGE function along with the AVERAGE function to find the average of a range spread in multiple columns. Such as, I will apply this combination of Excel functions to calculate the average of the top 3 values of the range B11:E11.

Steps:

• Type the below formula in cell B13 and press Enter.

• As a result, I will get the average of the top 3 values from the range B11:E11 which is spread over multiple columns.

Here, the LARGE function returns the 3 largest values (89, 87, & 77) in the range B11:E11. Later, the AVERAGE function returns the average of the above 3 numbers.

⏩ Note:
You can use the SMALL function along with the AVERAGE function to calculate the average of the smallest numbers in a range spread over multiple columns.

6. Excel OFFSET, AVERAGE, and COUNT Functions to Calculate the Average of Last N Values in Multiple Columns

I will use the OFFSET function along with the COUNT and AVERAGE functions to calculate the average of the last N values which are spread over several columns. Such as I will calculate the average of the last three (3) values of the range B5:F5 of my dataset (B4:F11).

Steps:

• Type the following formula in cell B13 and hit Enter.

• Consequently, you will get below average.

🔎 How Does the Formula Work?

➤ COUNT(B5:F5)
This part of the formula returns: {5}

➤ (OFFSET(B5,0,COUNT(B5:F5)-3,1,3)
Now, this part of the formula returns the last 3 values of the range B5:F5: {99,77,66}

➤ AVERAGE(OFFSET(B5,0,COUNT(B5:F5)-3,1,3))
Finally, the formula returns the average of the last 3 values (99,77,66) which is: {80.66666667}

Read More: How to Average Every Nth Row in Excel

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

In the above article, I have tried to discuss several methods to calculate the average of multiple columns in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Related Articles

• How to Calculate Average Only for Cells with Values in Excel
• How to Exclude a Cell in Excel AVERAGE Formula
• How to Find Average of Specific Cells in Excel
• How to Average Only Visible Cells in Excel
• How to Find Average with Blank Cells in Excel
• How to Fix Divide by Zero Error for Average Calculation in Excel
• How to Ignore #N/A Error When Getting Average in Excel
• [Fixed!] AVERAGE Formula Not Working in Excel

<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel