Hey everyone! I use Microsoft Office 365 for this particular file and I can't figure out how to average each metric on the AGENT sheet for each individual agent for the first 13 weeks. The metrics are in row 1. The only agents I want to average are the ones that are on the AGENT sheet. I tried =AVERAGEIF('Week 1'!A:'Week 13'!A,A2,'Week 1'!B2:'Week 13'!B2) in cell B2 of the AGENT sheet, but it gives me a #NAME error.
Thanks for any help in advance!!
First you have to make a named range. I utilized Q1:Q13.
I then put your worksheet names in these cells and named the range weeks
I then made the following formula which should work in in B2 and drag down:
Formula:
Please Login or Register to view this content.
Last edited by dosydos; 05-24-2022 at 02:27 PM.
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
What dosydos did not mention was that your syntax is incorrect, but even worse, AVERAGEIF does not work across multiple worksheets.
The correct syntax for a multi-worksheet range would be
'Week 1:Week 13'!$A:$A
which works only in a few functions such as SUM.
Thank you. both for your input. Is there any other way to achieve the end result without using AverageIF?
1) Put all data in 1 worksheet and after that a pivot table.
2) Use Powerpivot.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I tried this, but all I keep returning "0" instead of the correct value.
see my post #2 with excel sheet attached
I got it dosydos. Thank you!Originally Posted by dosydos
Power Query
Please Login or Register to view this content.
Last edited by Czeslaw; 05-25-2022 at 07:51 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks