Forum Discussion
Computing the average of numbers across multiple worksheets for an item in Sheet 1
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.
