Forum Discussion

TrippStrength's avatar
TrippStrength
Copper Contributor
Oct 23, 2024

Function Help

Hello,

 

I am trying to put together a spreadsheet that allows me to compare members of our team. We work in different "Sub" teams as you will. Just because you work on one team, does not mean your will not also be a part of another team. We encourage people to work on three different sub teams to allow them to work with others and allow for more free thinking ideas and strategies. I would like to be able to compare our team and see how well others work on different teams and try to objectively identify who really excels working with certain people and who does not. 

 

So my problem is when I try and use AVERAGEIFS or STDEV.S it only pulls the first available value. As the team and year changes I would Expect the average and the standard deviation to change as well. I have attached a spread sheet with my problem. Any clues with how to fix it would be greatly appreciated. 

 

Thank You,

Tripp

BI & Data Analysis
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Oct 24, 2024

    TrippStrength 

    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.

Resources