CODEWITHSUNDEEP
excelexcel-formulapivot-table
Daniel
Daniel

Reputation: 31

How to obtain filtered averages from the same column in Excel using several conditions?

For context, I have an excel Data Frame (we will call it SurveyData) containing quality scores provided to persons by two methods: AI and User Generated scores (See example below).

These scores come irregularly, and User generated scores tend to be lower in quantity (Usually a 1:20 ratio comparing with AI generated scores). User generated scores show as Blanks in the survey method column, they can start a survey but not finish it (Showing Abandoned in the status column and not providing any score, and AI generated scores don't generate any status, just the score. There can be multiple surveys for a each person on the same week.

SurveyData data frame:

Person Survey Method Score Status Week ending Access Key
A AI 3 10/27/2024 ID
B Abandoned 10/27/2024 ID
A AI 4 10/27/2024 ID
A 2 Completed 10/27/2024 ID
B AI 4 10/27/2024 ID
A 3 Completed 10/27/2024 ID
C AI 4 10/27/2024 ID
C Abandoned 10/27/2024 ID
A AI 2 10/27/2024 ID
B 4 Completed 10/27/2024 ID

I have a Pivot table that comes from another data frame (called PersonData) where I want to add the averaged survey score for each person each week, using the information from SurveyData.

Pivot table coming from AgentData table:

Week ending Person AI Score Avg U. Gen Score Avg
10/27/2024 A
B
C

Since both Data Frames can be crossed using a field called Access Key, which is unique per week and per person, I tried using this formula to get the average user generated score for each person using all scores in that week. The idea was to only use the scores of rows that have user generated scores (Blanks in Survey Method) and show as completed, to avoid adding blank scores into the equation, using the following formula:

=IF(XLOOKUP(AgentData[@[Access Key]],SurveyData[AccessKey],SurveyData[Survey Method],"", 0,1) = "", AVERAGE(FILTER(SurveyData[Score],AND(SurveyData[AccessKey] = AgentData[@[Access Key]], SurveyData[Status] = "Completed"))),
"")

This formula shows an error in the FILTER function, because the AND condition is always false, even if the conditions are true. I checked and standardized the data to avoid any typing errors but the issue persisted. When using one conditional the formula works, but I want to separate both score types because they have different weights due to the amount of surveys being independent for each type.

Can someone help me understand if there is a way I can do this alternatively? Combined Pivot tables could be used, but I would really like to grasp the concept of why this formula might not be working when adding two conditions. Thanks in advance.

Upvotes: 3

Views: 73

Answers (2)

nkalvi
nkalvi

Reputation: 2614

Reason for #CALC! error in FILTER(SurveyData[Score],AND(SurveyData[:

FILTER's include needs to be

A Boolean array whose height or width is the same as the array

Since AND always returns a single value and does not match the number of rows in SurveyData it results in error.

As Michal explained, use multiplication for AND:

=AVERAGE(
    FILTER(
        SurveyData[Score],
        (SurveyData[AccessKey] = [@[Access Key]]) *
            (SurveyData[Status] = "Completed"),
        0
    )
)

Result

Upvotes: 0

Michal
Michal

Reputation: 6131

I think this is what you are trying to accomplish:

=IFERROR(
     AVERAGE(
          FILTER($C$2:$C$11,($A$2:$A$11=I4)*($D$2:$D$11="Completed"))),"N/A")

The generic formula for FILTER with multiple criteria is:

=FILTER(range,(criteria1)*(criteria2),"Error message")

enter image description here

Upvotes: 0

Related Questions