excelexcel-formuladynamic

Dynamically averaging data Per ID Per Time period Alternate Version

enter image description here

I am looking to be able to select a User and then have the subsequent Metrics for each month and year populate in the calendar shown.

What i am measuring is the answers from Questions 1, 2, and 3 and the number of Yes answers against the total of Yes and No answers per month per User while the NAs are ignored to be counted against. It is being calculated with a =CountIF()/SUM(CountIFS()) Statement

I have been trying to use =COUNTIF(D2:F4,"Yes")/SUM(COUNTIFS(D2:F4,{"No","Yes"})) but its no feasible to do this for ongoing data individually per user per month

In the actual data there will be several entries for the same month and year so when the formula is made it must be able to accommodate for that

Sample Data Below:

Name Date Policy Question 1 Question 2 Question 3
Fernando Mullen 1/19/2023 732498 NA NA No
Fernando Mullen 1/25/2023 354924 Yes Yes Yes
Fernando Mullen 9/23/2024 669720 NA Yes No
Fernando Mullen 7/14/2024 602150 No Yes NA
Fernando Mullen 10/16/2023 644143 NA NA NA
Shay Gibson 4/25/2023 442807 Yes Yes Yes
Shay Gibson 12/4/2023 308642 NA NA No
Shay Gibson 12/28/2023 407548 NA NA NA
Shay Gibson 9/26/2023 237033 NA NA NA
Shay Gibson 12/19/2023 410126 NA NA NA
Tyler Long 4/11/2023 803544 NA NA No
Tyler Long 11/5/2024 399732 Yes Yes Yes
Tyler Long 11/11/2024 296820 NA NA NA
Tyler Long 3/27/2024 791065 NA NA NA
Wrenley Fleming 4/26/2024 463278 NA NA Yes
Wrenley Fleming 9/13/2024 774485 NA NA NA
Wrenley Fleming 9/15/2024 244185 No Yes NA
Wrenley Fleming 7/22/2024 506417 NA NA NA
Wrenley Fleming 12/26/2024 339160 NA NA NA
Solution

  • Try using the following formula:

    enter image description here

    =LET(
     a, FILTER($D$2:$F$20,
        ($I5&J$4=TEXT($B$2:$B$20,"mmmme"))*
        ($I$2=$A$2:$A$20),""),
     IFERROR(SUM(N(a="Yes"))/SUM(N(a<>"NA")),0))

    Alternatively, can use one single array formula to return the output for the whole array..

    =LET(
     _Month, I5:I16,
     _Year, J4:K4,
     _Date, TEXT(B2:B20,"mmmme"),
     _Name, A2:A20,
     MAKEARRAY(ROWS(_Month),COLUMNS(_Year),LAMBDA(x,y,
     LET(z, FILTER(D2:F20,(_Date=INDEX(_Month&_Year,x,y))*(_Name=I2),""),
     IFERROR(SUM(N(z="Yes"))/SUM(N(z<>"NA")),0)))))