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

In this tutorial, I will show some methods regarding 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.

Download Practice Workbook

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

6 Methods to Calculate Average of Multiple Columns in Excel

1. Calculate Average of Multiple Columns Using AVERAGE Function

Firstly, I will use simply the AVERAGE function to calculate the average of some adjacent ranges that are 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: Running Average: How to Calculate Using Excel’s Average(…) Function

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 that seems suitable to you, and press Enter. I have named the below ranges as ‘MultiCol’.

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

• Consequently, here is the ultimate average you will get.

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)

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 using 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 A and B. Now 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.

• As a consequence, I will get the average of the quantities of all ‘Apple’ of 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 with the excel cell. But, now I will search for a string that matches with the cell contents and then calculate the average from another column. For instance, in the below dataset I have a fruit name containing the string ‘Apple’ as part of the cell content (e.g. Wood Apple, Pineapple, etc.) So let’s 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.
• As a result, you will get the below result.

Read More: How to Calculate Average of Text in Excel (2 Ways)

Similar Readings

• Average Attendance Formula in Excel (5 Ways)
• How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)
• Get Average Time in Excel (3 Examples)
• How to Calculate 7 Day Moving Average in Excel (4 Ways)
• Calculate Average Percentage of Marks in Excel (Top 4 Methods)

4. Combination of AVERAGEIF and SUMIF Functions to Get 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:

• First, type the below formula in Cell E13 and hit Enter.

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

• In the end, 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 with 88 and returns:

{440}

Read More: [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)

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 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, and 77)  in the range B11:E11. Later, the AVERAGE function returns the average of 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.

Read More: Calculate Moving Average for Dynamic Range in Excel (3 Examples)

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

Now I will use the OFFSET function along with 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 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 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}

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 VLOOKUP AVERAGE in Excel (6 Quick Ways)
• Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
• How to Generate Moving Average in Excel Chart (4 Methods)
• Calculate Exponential Moving Average in Excel
• How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)
• Calculate Average of Text in Excel (2 Ways)
  
• How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)