Hi everyone,
I got the following dataset and I need to create two averageifs functions that are contigent on 3 conditions. The Final outcomes are two average figures of students' test scores.
Condition A: Select Rows with "Yes" responses. Do NOT select rows with "No" responses.
Condition B: Select Rows with value "2" responses.
Condition C: There's two scenarios for Condition C. In scenario 1, I want to select rows with value of 1 OR value of 1 and any other value (as indicated by a comma).
Condition C: Scenario 2: I want to select rows with multiple selections with specific constraints: (1) Count if a person has more than 1 selection (as denoted by a comma). (2) However, do not count if the student makes two selections AND the second selection contains number 6. (2) If a student makes more than two selections and there's a 6 in it, then count it.
Desired outCome #1 with Condition A, B, and first scenario of Condition C should yield an average test score of 84.38. For your convenience, I highlighted the relevant rows in green that go into the final average calculation.
Desired Outcome #2 with Condition A, B, and second senario of Condition C should yield an average test score of 70 where it's an average of the test scores for IDs 8, 30, 37, 41, 73
I've tried a variety of methods, as recommended on the internet but my numbers do not match with the expected answers.
I'd appreciate any assistance/guidance you can provide.
Thank you.
Anita
Bookmarks