I have the following spreasdheet:
| pserial | sex | marstatb | age | |
|---|---|---|---|---|
| 10105101 | 1 | 5 | 92 | |
| 10115101 | 1 | 1 | 63 | |
| 10120101 | 2 | 4 | 56 | |
| 10127101 | 1 | 2 | 38 | |
| 10127102 | 2 | 2 | 25 | |
| 10135101 | 2 | 1 | 37 | |
| 10135102 | 2 | -1 | 14 | |
| 10135103 | 2 | -1 | 10 | |
| 10137101 | 1 | 2 | 35 | |
| 10137102 | 2 | 2 | 29 |
Where pserial: Serial number of individual Sex: male/female marstatb: Marital status including cohabitees
I know that the person is divorced if marstatb= 4, and that the person is female if sex=2
I need to find the pserial value for the oldest divorced female.
I was thinking of using the filtering function, IF function for the criterias but I'm not sure how. I think the INDEX function would be useful for this too.
UPDATE: This is my function so far:
=@FILTER(A2:A9282,MAXIFS(D2:D9282,B2:B9282,2,C2:C9282,4)=D2:D9282)
CodePudding user response：
Using FILTER() & MAXIFS()
• Formula used in cell F2
=FILTER($A$2:$D$12,(MAXIFS(D2:D12,B2:B12,2,C2:C12,4)=D2:D12)*(B2:B12=2)*(C2:C12=4))
Another alternative is using INDEX() & AGGREGATE()
• Formula used in cell F4
=INDEX(A2:D12,AGGREGATE(15,7,ROW(A2:D12)/
((D2:D12=AGGREGATE(14,7,D2:D12/((B2:B12=2)*(C2:C12=4)),
ROW($ZZ1)))*(B2:B12=2)*(C2:C12=4))-1,1),0)
Using SORT() & TAKE()
• Formula used in cell F6
=LET(x,SORT(A2:D12,4,-1),
TAKE(FILTER(x,(INDEX(x,,2)=2)*(INDEX(x,,3)=4)),1))
With Headers:
=LET(x,SORT(A1:D12,4,-1),VSTACK(TAKE(x,1),
TAKE(FILTER(x,(INDEX(x,,2)=2)*(INDEX(x,,3)=4)),1)))
EDIT
For MS365 Users
• Formula used in cell F9
=TAKE(SORT(FILTER(A2:D12,(B2:B12=2)*(C2:C12=4)),4,-1),1)
With Headers:
• Formula used in cell F11
=VSTACK(A1:D1,
TAKE(SORT(FILTER(A2:D12,(B2:B12=2)*(C2:C12=4)),4,-1),1))