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
1. Calculate Average for 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 following 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.
2. Calculate Average for 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.
3. Video: Calculate Average among Multiple Different Worksheets
This tutorial video will show you a simple yet powerful formula-based method(AVERAGE) to efficiently compute averages across various sheets in Excel 2013/2016/2019/365.
4. SAMPLE FIlES
Below are sample files in Microsoft Excel that you can download for reference if you wish.
5. 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],…)….