How to Calculate Average among Multiple Different Worksheets in Excel

If we want to calculate the average for all numbers from multiple worksheets, how can we do calculate? Currently we may know the way to calculate the average for a selected range on one worksheet, this article will introduce you a convenient way to calculate the average for all numbers across multiple worksheets.

As the data range on multiple worksheets may be the same or different, so we prepare two examples to do demonstration.

Calculate the Average for The Same Selected Range from Multiple Different Worksheets


First, we need to prepare two worksheets with numbers entered in the same range. See example below.

Worksheet1->Season 1                             Worksheet2->Season 2

Calculate Average among Multiple Different Worksheets 1                    Calculate Average among Multiple Different Worksheets 2

a. Select a blank cell for saving the result, in this case we select E2.

Calculate Average among Multiple Different Worksheets 3

b. in E2, enter the formula =AVERAGE(‘Season 1:Season 2’!B2:B4).

Calculate Average among Multiple Different Worksheets 4

Notes:

‘Season 1:Season 2’ is the worksheet range. Season 1 and Season 2 are the worksheet names. If you enter ‘sheet1:sheet3’ in this field, that means the worksheet you selected includes sheet1, sheet2 and sheet3, total three adjacent worksheets. And user need to add quotes for ‘Season 1:Season 2’.

‘B2:B4’ is the data range you want to do calculate the average.

c. Click Enter to get the result.

Calculate Average among Multiple Different Worksheets 5

Calculate the Average for The Different Selected Ranges from Multiple Different Worksheets


First, we need to prepare two worksheets with numbers entered in the different ranges. See example below.

Worksheet1->Season 1                             Worksheet2->Season 2

Calculate Average among Multiple Different Worksheets 6                         Calculate Average among Multiple Different Worksheets 7

a. This time, in E2, enter the formula =AVERAGE(‘Season 1′!B2:B6,’Season 2’!B2:B5). The format is ‘worksheet!data range’.

Calculate Average among Multiple Different Worksheets 8

b. Click Enter to get the result.

Calculate Average among Multiple Different Worksheets 9

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

 

 

You might also like:

Sidebar