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.
Table of Contents
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
a. Select a blank cell for saving the result, in this case we select E2.
b. in E2, enter the formula =AVERAGE(‘Season 1:Season 2’!B2:B4).
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 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
a. This time, in E2, enter the formula =AVERAGE(‘Season 1′!B2:B6,’Season 2’!B2:B5). The format is ‘worksheet!data range’.
b. Click Enter to get the result.
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],…)….
