I have Table A and I want to create two new columns to get table B. I am trying to create a formula where I am only calculating the min or max of previous records for each person. Is there a formula that I can use to automatically fill in for the rest of the records? I have tried this formula but I received an error.
= MIN(IF(AND(C:C=$C2, A:A<$A2),D:D))
TABLE A:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SES | DATE | NAME | SCORE |
| 2 | 1 | 9/4/23 | Alice | 165 |
| 3 | 1 | 9/4/23 | Bob | 224 |
| 4 | 1 | 9/4/23 | Tina | 170 |
| 5 | 2 | 10/7/23 | Alice | 160 |
| 6 | 2 | 10/7/23 | Bob | 218 |
| 7 | 2 | 10/7/23 | Tina | 167 |
| 8 | 3 | 11/4/23 | Alice | 155 |
| 9 | 3 | 11/4/23 | Bob | 220 |
| 10 | 3 | 11/4/23 | Tina | 165 |
| 11 | 4 | 12/2/23 | Alice | 150 |
| 12 | 4 | 12/2/23 | Bob | 221 |
| 13 | 4 | 12/2/23 | Tina | 168 |
TABLE B:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SES | DATE | NAME | SCORE | MIN | MAX |
| 2 | 1 | 9/4/23 | Alice | 165 | NA | NA |
| 3 | 1 | 9/4/23 | Bob | 224 | NA | NA |
| 4 | 1 | 9/4/23 | Tina | 170 | NA | NA |
| 5 | 2 | 10/7/23 | Alice | 160 | 165 | 165 |
| 6 | 2 | 10/7/23 | Bob | 218 | 224 | 224 |
| 7 | 2 | 10/7/23 | Tina | 167 | 170 | 170 |
| 8 | 3 | 11/4/23 | Alice | 155 | 160 | 165 |
| 9 | 3 | 11/4/23 | Bob | 220 | 218 | 224 |
| 10 | 3 | 11/4/23 | Tina | 165 | 167 | 170 |
| 11 | 4 | 12/2/23 | Alice | 150 | 155 | 165 |
| 12 | 4 | 12/2/23 | Bob | 221 | 218 | 224 |
| 13 | 4 | 12/2/23 | Tina | 168 | 165 | 170 |
>Solution :
For min in E2 and dragged down:
=MINIFS($D$1:D1,$C$1:C1,C2)
For max in F2 and dragged down:
=MAXIFS($D$1:D1,$C$1:C1,C2)
Result:
