Excel Subtract BY Group

Question

STUDENT	CLASS	TIME	GRADE
1	A	1	5
1	A	2	8
1	A	3	6
1	B	1	2
1	B	2	9
2	A	1	2
2	B	1	6
2	C	1	3
2	C	2	0

I have the data above and wish for this data below,

STUDENT	CLASS	TIME	GRADE	WANT1	WANT2
1	A	1	5	3	-2
1	A	2	8	3	-2
1	A	3	6	3	-2
1	B	1	2	7
1	B	2	9	7
2	A	1	2
2	B	1	6
2	C	1	3	-3
2	C	2	0	-3

with the rules,

WANT1 == for each STUDENT and CLASS combination, GRADE(TIME = 2) - GRADE(TIME = 1)
WANT2 == for each STUDENT and CLASS combination, GRADE(TIME = 3) - GRADE(TIME = 2)

Answer 1

Subtract Filtered Data

• Both solutions work for the presented sample data.

XLOOKUP

• This is more efficient but doesn't allow Time duplicates for unique Student/Class values.

=LET(data,$A$2:$D$10,smaller_times,{1;2},greater_times,{2;3},
    ds,CHOOSECOLS(data,1),
    dc,CHOOSECOLS(data,2),
    dt,CHOOSECOLS(data,3),
    dg,CHOOSECOLS(data,4),
MAKEARRAY(ROWS(data),COUNT(smaller_times),LAMBDA(r,c,LET(
    fsc,(ds=INDEX(ds,r))*(dc=INDEX(dc,r)),
    ts,XLOOKUP(1,fsc*(dt=INDEX(smaller_times,c)),dg),
    tg,XLOOKUP(1,fsc*(dt=INDEX(greater_times,c)),dg),
IFERROR(tg-ts,"")))))

SUM/FILTER

• This is less efficient but allows Time duplicates for unique Student/Class values.

=LET(data,$A$2:$D$10,smaller_times,{1;2},greater_times,{2;3},
    ds,CHOOSECOLS(data,1),
    dc,CHOOSECOLS(data,2),
    dt,CHOOSECOLS(data,3),
    dg,CHOOSECOLS(data,4),
MAKEARRAY(ROWS(data),COUNT(smaller_times),LAMBDA(r,c,LET(
    fsc,(ds=INDEX(ds,r))*(dc=INDEX(dc,r)),
    ts,SUM(FILTER(dg,fsc*(dt=INDEX(smaller_times,c)))),
    tg,SUM(FILTER(dg,fsc*(dt=INDEX(greater_times,c)))),
IFERROR(tg-ts,"")))))

Answer 2

Assuming your data starts in cell A1, here's one possible solution:

For the "WANT1" column, use the following formula in cell E2 and copy it down:

=IF(PRODUCT(COUNTIFS($A$2:$A$10, A2, $B$2:$B$10, B2, $C$2:$C$10, {1,2}))>0,
MMULT(SUMIFS($D$2:$D$10, $A$2:$A$10, A2, $B$2:$B$10, B2, $C$2:$C$10, {1,2}), {-1;1}), "")

For the "WANT2" column, use the following formula in cell F2 and copy it down:

=IF(PRODUCT(COUNTIFS($A$2:$A$10, A2, $B$2:$B$10, B2, $C$2:$C$10, {2,3}))>0,
MMULT(SUMIFS($D$2:$D$10, $A$2:$A$10, A2, $B$2:$B$10, B2, $C$2:$C$10, {2,3}), {-1;1}), "")

NOTE: with older versions of Excel, be sure to use Ctrl+Shift+Enter.

With Office 365, the above methods can be combined into a single array formula in cell E2:

=LET(
rng, A2:D10,
_c1, INDEX(rng,, 1),
_c2, INDEX(rng,, 2),
_c3, INDEX(rng,, 3),
_c4, INDEX(rng,, 4),
MAKEARRAY(ROWS(rng), 2, LAMBDA(r,c,
   IF(PRODUCT(COUNTIFS(_c1, INDEX(_c1, r), _c2, INDEX(_c2, r), _c3, SEQUENCE(, 2, c)))>0,
   MMULT(SUMIFS(_c4, _c1, INDEX(_c1, r), _c2, INDEX(_c2, r), _c3, SEQUENCE(, 2, c)), {-1;1}), "")))
)

To create a condensed summary by STUDENT & CLASS:

=LET(
rng, A2:D10,
_c1, INDEX(rng,, 1),
_c2, INDEX(rng,, 2),
_c3, INDEX(rng,, 3),
_c4, INDEX(rng,, 4),
lst, UNIQUE(A2:B10),
arr, MAKEARRAY(ROWS(lst), 2, LAMBDA(r,c,
   IF(PRODUCT(COUNTIFS(_c1, INDEX(lst, r, 1), _c2, INDEX(lst, r, 2), _c3, SEQUENCE(, 2, c)))>0,
   MMULT(SUMIFS(_c4, _c1, INDEX(lst, r, 1), _c2, INDEX(lst, r, 2), _c3, SEQUENCE(, 2, c)), {-1;1}), ""))),
VSTACK({"STUDENT","CLASS","WANT1","WANT2"}, HSTACK(lst, arr))
)

Results:

Adjust the range references to meet your needs. Cheers!

Answer 3

WANT1:

=MAP(A2:A10,B2:B10,LAMBDA(x,y,IFERROR(FILTER(D2:D10,(A2:A10=x) 
 * (B2:B10=y) * (C2:C10=2))-FILTER(D2:D10,(A2:A10=x) * (B2:B10=y) 
 * (C2:C10=1)),"")))

WANT2:

=MAP(A2:A10,B2:B10,LAMBDA(x,y,IFERROR(FILTER(D2:D10,(A2:A10=x) 
 * (B2:B10=y) * (C2:C10=3))-FILTER(D2:D10,(A2:A10=x) * (B2:B10=y) 
 * (C2:C10=2)),"")))

Can't attach a screenshot since i'm a new user. The sample data is in range A2:A10 (STUDENT), B2:B10 (CLASS), C2:C10 (TIME) and D2:D10 (GRADE).The formulas can be entered in cells E2 (WANT1) and F2 (WANT2).

Answer 4

Similar idea to @user22566114. The question assumes there is a single value for each case that match the conditions, so we can use FILTER function as follows:

=LET(in,A2:D10, A,INDEX(in,,1), B,INDEX(in,,2), C,INDEX(in,,3), D,INDEX(in,,4),
 CALC, LAMBDA(ta,tb, MAP(A,B,C,D, LAMBDA(aa,bb,cc,dd,
  LET(fa,FILTER(D,(C=ta)*(A=aa)*(B=bb),""),
   fb,FILTER(D,(C=tb)*(A=aa)* (B=bb),""),
    IF(OR(@fa="", @fb=""), "", IF(ROWS(fa)*ROWS(fb)=1,fa-fb,"ERROR")))))),
 HSTACK(CALC(2,1), CALC(3,2)))

Here is the output:

To avoid repetition of similar calculations we create a user LAMBDA function CALC, then we call it with the specific parameters for each column. The IF condition identifies the following scenarios:

1. OR(@fa="", @fb="") any empty filter result, then it returns an empty string.
2. IF(ROWS(fa)*ROWS(fb)=1: The filter result is a single row on each case (the assumption we are considering), so we return fa-fb.
3. Else, then it warns the assumption is not valid returning an ERROR string for the given rows.

If we replace C4: 3 -> 2. The approach provided by @user22566114 returns #CALC!, for the entire result, so we don't know where is the issue. Under this approach it indicates the lines that don't satisfy the condition with the ERROR token, so the user can easily identify the issue and correct the input values: