| STUDENT | TIME | CLASS | SCORE | WANT |
|---|---|---|---|---|
| 1 | 1 | A | 13 | NULL |
| 1 | 1 | B | 4 | NULL |
| 1 | 2 | A | 11 | -2 |
| 1 | 2 | B | 9 | 5 |
| 1 | 3 | A | 8 | -3 |
| 2 | 2 | B | 16 | NULL |
| 2 | 3 | B | 6 | -10 |
| 2 | 4 | A | 7 | NULL |
| 2 | 4 | B | 6 | 0 |
I have XLSX file with STUDENT, TIME, CLASS, SCORE. I wish to calculate WANT which does this:
For every STUDENT and CLASS, calculate the difference in SCORE from TIME(X) TO TIME(X-1).
for STUDENT=1, TIME=2,CLASS=B equals to 5 because it is (9-4)
I try this with no success:
=IF(A3=A2 & C3=C2, OFFSET(D3, -1, 0), "")
CodePudding user response：
I think you can try:
Formula in E2:
=IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)>1,D2-SUMIFS(D:D,A:A,A2,B:B,B2-1,C:C,C2),"Null")
CodePudding user response：
It is far from the best approach, but it works.
If using helper column is not a problem, you can make additional column for VLOOKUP (see column "Helper1") with formula =TEXTJOIN("-",,A2:C2).
Now use VLOOKUP to find value TEXTJOIN("-",,A2,B2-1,C2) in that column. Formula in "WANT" column: IFNA(E2-VLOOKUP(TEXTJOIN("-",,A2,B2-1,C2),$D$2:$E$10,2,FALSE),"NULL")
Result:
