Forum Discussion
Function Help
For the average, I proposed
=IFERROR(AVERAGE(FILTER(INDIRECT("Sales["&$B3&"]"), ((Sales[[Team 1]:[Team 1]]=$H$2)+(Sales[[Team 2]:[Team 2]]=$H$2)+(Sales[[Team 3]:[Team 3]]=$H$2))*(Sales[[Year]:[Year]]=$H$3))), "")
This includes Team 1, Team 2 and Team 3.
You used
=IFERROR(AVERAGEIFS(Sales[Jan],Sales[[Year]:[Year]],$H$3,Sales[[Team 1]:[Team 1]],$H$2),"")
which only looks at Team 1.
I have attached the workbook with my formulas again.
I wish I could Just add the Excel Book for y'all. If anyone Is able to do tell me how to do that that would be greatly appreciated!
Tripp
Upload the workbook to a cloud service such as OneDrive, Google Drive or Dropbox.
Obtain a link to the uploaded file that allows others to view/download the file.
Paste that link into a reply.
- Thank You! I have attached the link.
Thanks! See the attached version. I used FILTER combined with AVERAGE and STDEV.S, and a different way to write the criteria. We cannot use OR here because OR returns a single TRUE/FALSE value instead of an array.
I corrected two column names in the Sales table: "March " > "March" and "Sepetemeber" > "September".
- Thank You!!
What about for the averages? Hot pink for example has not data in the "team 1". But has data "Team 2" & "Team 3". How to I adjust the average.