I want the results in column "E" to be the same as the results in column "K", which have two formulas that calculate the deduction as shown in start Column "N"and end Column "M" consecutive numbers in the series for each group
and summarize both Formula
and summarize both Formula
Excel Formula:
INDEX($I2:I$13,IF(SUMPRODUCT(--(H3:$H$14-I2:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H2:$H$13)-ROW($H2)+1)/(--(H3:$H$14-I2:$I$13<>1)),1)))
Excel Formula:
INDEX($H$2:$H$13,IF(ROWS($H$2:H2)=1,1,IF(SUMPRODUCT(--($H2:H$3-$I1:I$2<>1))=0,1,AGGREGATE(14,6,(ROW($H2:H$3)-ROW($H$3)+1)*(--($H2:H$3-$I1:I$2<>1))+1,1))))
Book1.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | Grp | S | E | ??? | Grp | S | E | D | E | S | D | |||||||
2 | A | 1 | 5 | 9 | A | 1 | 5 | 9 | 10 | 1 | 9 | |||||||
3 | B | 7 | 12 | 13 | 6 | 10 | 9 | 10 | 1 | 9 | ||||||||
4 | C | 9 | 14 | 15 | 13 | 15 | 27 | 40 | 13 | 27 | ||||||||
5 | A | 6 | 10 | 9 | 16 | 20 | 27 | 40 | 13 | 27 | ||||||||
6 | A | 13 | 15 | 27 | 21 | 25 | 27 | 40 | 13 | 27 | ||||||||
7 | B | 13 | 17 | 13 | 26 | 30 | 27 | 40 | 13 | 27 | ||||||||
8 | C | 15 | 24 | 15 | 31 | 35 | 27 | 40 | 13 | 27 | ||||||||
9 | A | 16 | 20 | 27 | 36 | 40 | 27 | 40 | 13 | 27 | ||||||||
10 | C | 30 | 40 | 10 | 43 | 45 | 17 | 60 | 43 | 17 | ||||||||
11 | A | 21 | 25 | 27 | 46 | 50 | 17 | 60 | 43 | 17 | ||||||||
12 | A | 26 | 30 | 27 | 51 | 55 | 17 | 60 | 43 | 17 | ||||||||
13 | B | 18 | 20 | 13 | 56 | 60 | 17 | 60 | 43 | 17 | ||||||||
14 | C | 45 | 50 | 5 | ||||||||||||||
15 | A | 31 | 35 | 27 | C | 7 | 12 | 13 | 20 | 7 | 13 | |||||||
16 | B | 23 | 27 | 12 | 13 | 17 | 13 | 20 | 7 | 13 | ||||||||
17 | C | 55 | 60 | 15 | 18 | 20 | 13 | 20 | 7 | 13 | ||||||||
18 | A | 36 | 40 | 27 | 23 | 27 | 12 | 35 | 23 | 12 | ||||||||
19 | C | 61 | 70 | 15 | 28 | 35 | 12 | 35 | 23 | 12 | ||||||||
20 | A | 43 | 45 | 17 | 37 | 40 | 3 | 40 | 37 | 3 | ||||||||
21 | B | 28 | 35 | 12 | ||||||||||||||
22 | B | 37 | 40 | 3 | A | 9 | 14 | 15 | 24 | 9 | 15 | |||||||
23 | A | 46 | 50 | 17 | 15 | 24 | 15 | 24 | 9 | 15 | ||||||||
24 | C | 75 | 80 | 15 | 30 | 40 | 10 | 40 | 30 | 10 | ||||||||
25 | A | 51 | 55 | 17 | 45 | 50 | 5 | 50 | 45 | 5 | ||||||||
26 | C | 81 | 90 | 15 | 55 | 60 | 15 | 70 | 55 | 15 | ||||||||
27 | A | 56 | 60 | 17 | 61 | 70 | 15 | 70 | 55 | 15 | ||||||||
28 | 75 | 80 | 15 | 90 | 75 | 15 | ||||||||||||
29 | 81 | 90 | 15 | 90 | 75 | 15 | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M2:M13 | M2 | =INDEX($I2:I$13,IF(SUMPRODUCT(--(H3:$H$14-I2:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H2:$H$13)-ROW($H2)+1)/(--(H3:$H$14-I2:$I$13<>1)),1))) |
N2:N3 | N2 | =INDEX($H$2:$H$13,IF(ROWS($H$2:H2)=1,1,IF(SUMPRODUCT(--($H2:H$3-$I1:I$2<>1))=0,1,AGGREGATE(14,6,(ROW($H2:H$3)-ROW($H$3)+1)*(--($H2:H$3-$I1:I$2<>1))+1,1)))) |
N4:N13 | N4 | =INDEX($H$2:$H$13,IF(ROWS($H$2:H4)=1,1,IF(SUMPRODUCT(--($H$3:H4-$I$2:I3<>1))=0,1,AGGREGATE(14,6,(ROW($H$3:H4)-ROW($H$3)+1)*(--($H$3:H4-$I$2:I3<>1))+1,1)))) |
K2:K3 | K2 | =INDEX($I2:I$13,IF(SUMPRODUCT(--(H3:$H$14-I2:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H2:$H$13)-ROW($H2)+1)/(--(H3:$H$14-I2:$I$13<>1)),1)))-INDEX($H$2:$H$13,IF(ROWS($H$2:H2)=1,1,IF(SUMPRODUCT(--($H2:H$3-$I1:I$2<>1))=0,1,AGGREGATE(14,6,(ROW($H2:H$3)-ROW($H$3)+1)*(--($H2:H$3-$I1:I$2<>1))+1,1)))) |
K4:K13 | K4 | =INDEX($I4:I$13,IF(SUMPRODUCT(--(H5:$H$14-I4:$I$13<>1))=0,1,AGGREGATE(15,6,(ROW(H4:$H$13)-ROW($H4)+1)/(--(H5:$H$14-I4:$I$13<>1)),1)))-INDEX($H$2:$H$13,IF(ROWS($H$2:H4)=1,1,IF(SUMPRODUCT(--($H$3:H4-$I$2:I3<>1))=0,1,AGGREGATE(14,6,(ROW($H$3:H4)-ROW($H$3)+1)*(--($H$3:H4-$I$2:I3<>1))+1,1)))) |
P22:P29,P15:P20,P2:P13 | P2 | =M2-N2 |
M15:M20 | M15 | =INDEX($I15:I$20,IF(SUMPRODUCT(--(H16:$H$21-I15:$I$20<>1))=0,1,AGGREGATE(15,6,(ROW(H15:$H$20)-ROW($H15)+1)/(--(H16:$H$21-I15:$I$20<>1)),1))) |
N15:N16 | N15 | =INDEX($H$15:$H$20,IF(ROWS($H$15:H15)=1,1,IF(SUMPRODUCT(--($H15:H$16-$I14:I$15<>1))=0,1,AGGREGATE(14,6,(ROW($H15:H$16)-ROW($H$16)+1)*(--($H15:H$16-$I14:I$15<>1))+1,1)))) |
N17:N20 | N17 | =INDEX($H$15:$H$20,IF(ROWS($H$15:H17)=1,1,IF(SUMPRODUCT(--($H$16:H17-$I$15:I16<>1))=0,1,AGGREGATE(14,6,(ROW($H$16:H17)-ROW($H$16)+1)*(--($H$16:H17-$I$15:I16<>1))+1,1)))) |
K15:K16 | K15 | =INDEX($I15:I$20,IF(SUMPRODUCT(--(H16:$H$21-I15:$I$20<>1))=0,1,AGGREGATE(15,6,(ROW(H15:$H$20)-ROW($H15)+1)/(--(H16:$H$21-I15:$I$20<>1)),1)))-INDEX($H$15:$H$20,IF(ROWS($H$15:H15)=1,1,IF(SUMPRODUCT(--($H15:H$16-$I14:I$15<>1))=0,1,AGGREGATE(14,6,(ROW($H15:H$16)-ROW($H$16)+1)*(--($H15:H$16-$I14:I$15<>1))+1,1)))) |
K17:K20 | K17 | =INDEX($I17:I$20,IF(SUMPRODUCT(--(H18:$H$21-I17:$I$20<>1))=0,1,AGGREGATE(15,6,(ROW(H17:$H$20)-ROW($H17)+1)/(--(H18:$H$21-I17:$I$20<>1)),1)))-INDEX($H$15:$H$20,IF(ROWS($H$15:H17)=1,1,IF(SUMPRODUCT(--($H$16:H17-$I$15:I16<>1))=0,1,AGGREGATE(14,6,(ROW($H$16:H17)-ROW($H$16)+1)*(--($H$16:H17-$I$15:I16<>1))+1,1)))) |
M22:M29 | M22 | =INDEX($I22:I$29,IF(SUMPRODUCT(--(H23:$H$30-I22:$I$29<>1))=0,1,AGGREGATE(15,6,(ROW(H22:$H$29)-ROW($H22)+1)/(--(H23:$H$30-I22:$I$29<>1)),1))) |
N22:N23 | N22 | =INDEX($H$22:$H$29,IF(ROWS($H$22:H22)=1,1,IF(SUMPRODUCT(--($H22:H$23-$I21:I$22<>1))=0,1,AGGREGATE(14,6,(ROW($H22:H$23)-ROW($H$23)+1)*(--($H22:H$23-$I21:I$22<>1))+1,1)))) |
N24:N29 | N24 | =INDEX($H$22:$H$29,IF(ROWS($H$22:H24)=1,1,IF(SUMPRODUCT(--($H$23:H24-$I$22:I23<>1))=0,1,AGGREGATE(14,6,(ROW($H$23:H24)-ROW($H$23)+1)*(--($H$23:H24-$I$22:I23<>1))+1,1)))) |
K22:K23 | K22 | =INDEX($I22:I$29,IF(SUMPRODUCT(--(H23:$H$30-I22:$I$29<>1))=0,1,AGGREGATE(15,6,(ROW(H22:$H$29)-ROW($H22)+1)/(--(H23:$H$30-I22:$I$29<>1)),1)))-INDEX($H$22:$H$29,IF(ROWS($H$22:H22)=1,1,IF(SUMPRODUCT(--($H22:H$23-$I21:I$22<>1))=0,1,AGGREGATE(14,6,(ROW($H22:H$23)-ROW($H$23)+1)*(--($H22:H$23-$I21:I$22<>1))+1,1)))) |
K24:K29 | K24 | =INDEX($I24:I$29,IF(SUMPRODUCT(--(H25:$H$30-I24:$I$29<>1))=0,1,AGGREGATE(15,6,(ROW(H24:$H$29)-ROW($H24)+1)/(--(H25:$H$30-I24:$I$29<>1)),1)))-INDEX($H$22:$H$29,IF(ROWS($H$22:H24)=1,1,IF(SUMPRODUCT(--($H$23:H24-$I$22:I23<>1))=0,1,AGGREGATE(14,6,(ROW($H$23:H24)-ROW($H$23)+1)*(--($H$23:H24-$I$22:I23<>1))+1,1)))) |