Forum Discussion

TrippStrength's avatar
TrippStrength
Copper Contributor
Oct 23, 2024
Solved

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...
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.

TrippStrength's avatar
TrippStrength
Copper Contributor
Oct 24, 2024
https://1drv.ms/x/s!AsTpsZBaeWzBdFoUhLFAi8EjW6g?e=4R5jaE&nav=MTVfezM5QTU5RjEzLTM3QzYtNDY3MC1CQ0M5LUVFNjc5MjYxOTlDMX0

This where I am at. I made the changes you suggested. Someone messed with the original book. So now this is in "read only" mode. I do really appreciate your help.
HansVogelaar's avatar
HansVogelaar
MVP
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.

  • TrippStrength's avatar
    TrippStrength
    Copper Contributor
    Oct 25, 2024
    Thank You again!!

    I need to learn how to use Filter and Indirect formulas

Resources