Annyca Tabassum
In Google Sheets, calculating the average is the most common and basic function. We use this equation most often for different purposes. So, in this article, we will show how to average cells from different sheets in Google Sheets.
Steps to Average Cells from Different Sheets in Google Sheets
You can simply average cells by using the AVERAGE function or applying mathematical techniques. Here, to calculate the average from two different sheets we use the AVERAGE function between two sheets.
Step 1: Create Different Sheets
To show the average from different cells we develop two datasets which are located in two spreadsheets. We name the sheets: Sheet1 and Sheet2.
- Sheet1 contains the columns Month and Sales for a specific year.
- On the other hand, Sheet2 represents the same column name with different sales data for the same months.
- We develop Sheet3 to show the average value of these two sheets.
Read More: How to Add Average Line in Google Sheets (With Detailed Steps)
Step 2: Apply AVERAGE Function
To get the average of Sheet1 and Sheet2 you have to apply the AVERAGE function in Sheet3. To do so,
- First, select cell C4 in Sheet3.
- Now insert the AVERAGE function in the cell.
- Then, input the Sheet1 sales values as the range.
=AVERAGE(Sheet1!C5:C10
- After that, add the Sheet2 values in the function.
=AVERAGE(Sheet1!C5:C10,Sheet2!C5:C10
Read More: Calculate Average of Last N Rows in Google Sheets (3 Ways)
Step 3: Final Outcome
After inserting values from Sheet1 and Sheet2 you have to go to Sheet3 to get the final output.
- Finally, press ENTER, and you will find the average of these two sheets in Sheet3.
=AVERAGE(Sheet1!C5:C10,Sheet2!C5:C10)
How to Average Cells from Different Spreadsheets in Google Sheets
You can also calculate the average from data located in different spreadsheets. To do so, all you need is to import data ranging from separate spreadsheets and input it into the AVERAGE function. We use the IMPORTRANGE function that will help us to import data from one spreadsheet to another spreadsheet in a quick and easiest way. The syntax of the IMPORTRANGE function is:
=IMPORTRANGE([spreadsheet_url], [range_string])Here, spreadsheet_url is the URL address for a specific spreadsheet and range_string represents the data range that you want to import.
Steps:
- We now have a separate spreadsheet containing two sheets: Sheet1 & Sheet2. Both sheets contain the same sales data for several months.
- Now go to the Main spreadsheet and select cell E4 to insert the AVERAGE function.
- Insert the AVERAGE function.
- After that add the different spreadsheets data as values in the function through the IMPORTRANGE function.
=AVERAGE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1sVCyHYWzVnikrlIbRrzxVXlHug5DjZ4XYxVkzejh788/edit#gid=0","Sheet1!C5:C10"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/1sVCyHYWzVnikrlIbRrzxVXlHug5DjZ4XYxVkzejh788/edit#gid=562588960","Sheet2!C5:C10"))
Formula Breakdown:
- https://docs.google.com/spreadsheets…: Represents the URL address of the sheet.
- Sheet1!C5:C10: This is the range of cells to import from the other spreadsheet. The worksheet targeted is Sheet1.
- IMPORTRANGE(“https://docs.google.com/spreadsheets….”,”Sheet1!C5:C10″): Here we are importing the selected data range from Sheet1 of the second worksheet.
- IMPORTRANGE(“https://docs.google.com/spreadsheets/d/..”,”Sheet2!C5:C10“): Similarly, this section represents the import range of Sheet2 of the second worksheet.
- AVERAGE(IMPORTRANGE(“https://docs.google.com/spreadsheets..”,”Sheet1!C5:C10″),IMPORTRANGE(“https://docs.google.com/spreadsheets/d/..”,”Sheet2!C5:C10″)): This represents the final AVERAGE of the data collected for the two sheets.
- Finally, press ENTER, and you will find the average value in the selected cells.
Things to Remember
- Be careful about sheet selection.
- Insert the range properly in the function.
Conclusion
After completing this article, we hope you will gain quite good knowledge about how to average cells from different sheets in Google Sheets. Moreover, to explore more about averages in Google Sheets you can visit the OfficeWheel website.
