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

Get FREE Advanced Excel Exercises with Solutions!

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.

Calculate Average of Multiple Columns Using AVERAGE FunctionSteps:

  • Type the below formula in Cell B13 to calculate the average of ranges B5:B10, C5:D9, and E6:E11.
=AVERAGE(B5:B10,C5:D9,E6:E11)

Calculate Average of Multiple Columns Using AVERAGE Function

  • 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’.

Define a Name to Multiple Columns and Then Get the Average

  • Now type the below formula in Cell B13 and hit Enter.
=AVERAGE(MultiCol)

Define a Name to Multiple Columns and Then Get the Average

  • 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.

Get Average of Cells that Match a Criteria Exactly

Steps:

  • Type the following formula in Cell C14 and hit Enter.
=AVERAGEIF(B5:B12,"Apple",C5:C12)

Get Average of Cells that Match a Criteria Exactly

  • 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.

Calculate Average of Cells that Match Criteria in a String

Steps:

  • Type the below formula in Cell C14.
=AVERAGEIF(B5:B12,"*Apple*",C5:C12)

Calculate Average of Cells that Match Criteria in a String

  • 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


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.

Combination of AVERAGEIF and SUMIF Functions to Get Average of Multiple Columns

Steps:

  • First, type the below formula in Cell E13 and hit Enter.
=AVERAGEIF($B$5:$B$10,B13,$E$5:$E$10)*SUMIF($B$5:$B$10,B13,$C$5:$C$10)

Combination of AVERAGEIF and SUMIF Functions to Get Average of Multiple Columns

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

Combination of AVERAGEIF and SUMIF Functions to Get Average of Multiple Columns

  • 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.

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

Steps:

  • Type the below formula in Cell B13 and press Enter.
=AVERAGE(LARGE(B11:E11, {1,2,3}))

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

  • 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.
=AVERAGE(OFFSET(B5,0,COUNT(B5:F5)-3,1,3))

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

  • 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

Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo