Forum Discussion
Computing the average of numbers across multiple worksheets for an item in Sheet 1
I have an Excel Workbook that has a Summary Sheet (Sheet 1) and then 19 other sheets, each representing a year from 2000-2018. Each sheet contains the following columns:
1) Ranking
2) Manager/Team Name
3) Annual Payroll
4) Percent of Annual Payroll as compared to the Average Payroll for that year
5) Percent of Annual Payroll as compared to the Median Payroll for that year
In the Summary sheet, I would like to show each
1) Manager/Team Name
2) Their 19 year average payroll
3) The 19 year average of their Percent of Annual Payroll as compared to the Average Payroll for that year
What is the simplest way to find the two averages?
What is the simplest way to find difference between the sum of the 19 year Payrolls versus the average for each year?
Thank you.
3 Replies
- To give credit to whom it is due, I learned the 3D Formula trick from Microsoft Excel MVP Bob Umlas, particularly Tip No. 85 on page 166 of his book: This isn't Excel, it's Magic!
In the attached file, the formula in Summary!B2 is:
=AVERAGE('2000:2018'!C2)
Note that the file only contains Sheets for 2000, 2001, 2002, and 2018. You can insert Sheets for 2003 to 2017 before 2018, without any required modification of the formulas in the Summary sheet.
But wait, there's more! You might be thinking that I typed the foregoing formula as you see it. My answer is a Big NO!
I actually typed this formula:
=AVERAGE('????'!C2)
Lo and behold, EXCEL automatically converted it to:
=AVERAGE('2000:2018'!C2)
Thank you. Will this work to essentially "lookup" the manager, who's name may not be in the same room in each sheet. If in 2000 you had a higher payroll than me and my team, you would appear ahead of me. If the following year Joe's team moved from #10 highest in 2000 to the highest payroll in 2001, Joe would appear ahead of both of us. I'll try your proposed solution shortly. What if different managers joined and others left? Simple example of having managers in different order attached.
