CODEWITHSUNDEEP
excelexcel-formula
Gary C
Gary C

Reputation: 3

Return value based on criteria (moving window, count))

I tried to think of a formula to get the value for column C and D but not success. Please Help.

Name YYYYMMDD Column C Coumn D
Mary 2023/1/1
Mary 2023/2/1 1
Mary 2023/3/1 1 1
Mary 2026/4/1
Mary 2026/5/1 1
Mary 2026/6/1 1 1
Peter 2021/1/1
Peter 2021/2/1 1
Peter 2022/4/1
Peter 2022/5/1 1
Peter 2025/6/1
Peter 2025/7/1 1
Peter 2025/8/1 1 1

Column C criteria : Return 1 when there are 2 or more record for column A(e.g Mary) in the last 12 months in column B

e.g. Cell C3 return 1 because there are 2 records (2023/2/1 and 2023/3/1) for Mary in the last 12 months

Column D criteria : Return 1 when there are 3 or more records for column A(e.g Mary)in the 12 months in column B

e.g. Cell D4 return 1 because there are 3 records (2023/1/1, 2023/2/1 and 2023/3/1) for Mary in the last 12 months

Assumption for Column B:

1: There is only 1 record for each person in each month 2: always the 1 day of each month

Upvotes: 0

Views: 47

Answers (1)

David Leal
David Leal

Reputation: 6759

Assuming no Excel version constraints as per the tags listed in the question, you can try the following in cell F2:

=LET(A, A2:A14, B, B2:B14, CALC, LAMBDA(p, MAP(A, B, LAMBDA(x, y, 
 LET(ref, EOMONTH(y,-12)+1, f, FILTER(B, (A=x) * (B>=ref) * (B<=y),0),
 IF(@f=0 ,"", IF(ROWS(f) >= p, 1, "")))))), HSTACK(CALC(2), CALC(3)))

Here is the output: output

We define a user LAMBDA function CALC with an input parameter (p), to avoid repetition of a similar calculation.

For an older Excel version, you can try the following for column F

=IF(SUM(($A$2:$A$14=A2) * ($B$2:$B$14>=(EOMONTH(B2,-12)+1)) 
 * ($B$2:$B$14<=B2))>=2,1,"")

and drag the formula down. For column G, do a similar operation, and replace >=2with >=3.

Upvotes: 0

Related Questions