Book9 | |||||
---|---|---|---|---|---|
C | D | E | |||
1 | Days | MAX (pre 365) | MAX (365) | ||
2 | 5 | 5 | 5 | ||
3 | 0 | 0 | 0 | ||
4 | 8 | 8 | 8 | ||
5 | 0 | 1 | 1 | ||
6 | 1 | 1 | 1 | ||
7 | 8 | 8 | 8 | ||
8 | 6 | 6 | 6 | ||
9 | 6 | 6 | 6 | ||
10 | 8 | 8 | 8 | ||
11 | 8 | 8 | 8 | ||
12 | 0 | 4 | 4 | ||
13 | 4 | 4 | 4 | ||
14 | 3 | 4 | 4 | ||
15 | 8 | 8 | 8 | ||
16 | 7 | 7 | 7 | ||
17 | 0 | 4 | 4 | ||
18 | 4 | 4 | 4 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D18 | D2 | =MAX((A2=$A$2:$A$18)*$C$2:$C$18) |
E2:E18 | E2 | =MAX((A2=$A$2:$A$18)*$C$2:$C$18) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ClientNo | invoice amount | days | Desired answer | |||
2 | 169727 | 1 | 5 | 5 | 5 | ||
3 | 694504 | 2 | 0 | 0 | 0 | ||
4 | 831381 | 3 | 8 | 8 | 8 | ||
5 | 869212 | 4 | 0 | 1 | 1 | ||
6 | 869212 | 5 | 1 | 1 | 1 | ||
7 | 317446 | 6 | 8 | 8 | 8 | ||
8 | 778941 | 7 | 6 | 6 | 6 | ||
9 | 275169 | 8 | 6 | 6 | 6 | ||
10 | 652275 | 9 | 8 | 8 | 8 | ||
11 | 761679 | 10 | 8 | 8 | 8 | ||
12 | 826323 | 11 | 0 | 4 | 4 | ||
13 | 826323 | 12 | 4 | 4 | 4 | ||
14 | 826323 | 13 | 3 | 4 | 4 | ||
15 | 584636 | 14 | 8 | 8 | 8 | ||
16 | 700030 | 15 | 7 | 7 | 7 | ||
17 | 605836 | 16 | 0 | 4 | 4 | ||
18 | 605836 | 17 | 4 | 4 | 4 | ||
Sheet26 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D18 | D2 | =MAXIFS(C:C,A:A,A2) |
E2:E18 | E2 | =AGGREGATE(14,6,C$2:C$18/(A$2:A$18=A2),1) |