lrobbo314
Well-known Member
- Joined
- Jul 14, 2008
- Messages
- 3,963
- Office Version
- 365
- Platform
- Windows
FILLDOWN: The value of a previous cell is propagated to the null-valued cells below in the columns specified.
FILLUP: The opposite.
FILLUP uses a helper LAMBDA `REVTABLE`, which is listed below.
FILLDOWN
FILLUP
REVTABLE
FILLUP: The opposite.
FILLUP uses a helper LAMBDA `REVTABLE`, which is listed below.
FILLDOWN/FILLUP | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | FILLDOWN w/ error | FILLDOWN w/ zero | FILLDOWN w/ blank | FILLUP w/ error | FILLUP w/ zero | FILLUP w/ blank | ||||||||||||||
2 | ||||||||||||||||||||
3 | 1 | 1 | 1 | 1 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | |||||||||
4 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
5 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
6 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
7 | #N/A | 1 | 0 | 1 | 1 | #N/A | 3 | 0 | 3 | 3 | ||||||||||
8 | 2 | 2 | 2 | 2 | 2 | 2 | #N/A | 3 | 0 | 3 | 3 | |||||||||
9 | #N/A | 2 | 0 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | |||||||||
10 | #N/A | 2 | 0 | 2 | 2 | #N/A | 2 | 0 | 2 | 2 | ||||||||||
11 | #N/A | 2 | 0 | 2 | 2 | #N/A | 2 | 0 | 2 | 2 | ||||||||||
12 | 3 | 3 | 3 | 3 | 3 | 3 | #N/A | 2 | 0 | 2 | 2 | |||||||||
13 | #N/A | 3 | 0 | 3 | 3 | 2 | 2 | 2 | 2 | 2 | 2 | |||||||||
14 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
15 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
16 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
17 | #N/A | 3 | 0 | 3 | 3 | #N/A | 1 | 0 | 1 | 1 | ||||||||||
18 | #N/A | 3 | 0 | 3 | 3 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||
19 | ||||||||||||||||||||
20 | ||||||||||||||||||||
21 | A | A | A | A | A | A | #N/A | C | 0 | C | C | |||||||||
22 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
23 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
24 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
25 | #N/A | A | 0 | A | A | #N/A | C | 0 | C | C | ||||||||||
26 | B | B | B | B | B | B | #N/A | C | 0 | C | C | |||||||||
27 | #N/A | B | 0 | B | B | C | C | C | C | C | C | |||||||||
28 | #N/A | B | 0 | B | B | #N/A | B | 0 | B | B | ||||||||||
29 | #N/A | B | 0 | B | B | #N/A | B | 0 | B | B | ||||||||||
30 | C | C | C | C | C | C | #N/A | B | 0 | B | B | |||||||||
31 | #N/A | C | 0 | C | C | B | B | B | B | B | B | |||||||||
32 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
33 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
34 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
35 | #N/A | C | 0 | C | C | #N/A | A | 0 | A | A | ||||||||||
36 | #N/A | C | 0 | C | C | A | A | A | A | A | A | |||||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3:B18,H21:H36,E21:E36,B21:B36,H3:H18,E3:E18 | B3 | =FILLDOWN(A3:A18) |
L3:L18,R21:R36,O21:O36,L21:L36,R3:R18,O3:O18 | L3 | =FILLUP(K3:K18) |
A4:A7,A31:A36,A27:A29,A22:A25,A13:A18,A9:A11 | A4 | =NA() |
Dynamic array formulas. |
FILLDOWN
Excel Formula:
=LAMBDA(range,
LET(
r,IF((range=0)+(range="")+ISERROR(range),NA(),range),
SCAN(0,r,LAMBDA(s,c,IF(ISERROR(c),s,c)))
)
)
FILLUP
Excel Formula:
=LAMBDA(range,
REVTABLE(FILLDOWN(REVTABLE(range)))
)
REVTABLE
Excel Formula:
=LAMBDA(table,
LET(
d,table,
r,ROWS(d),
INDEX(d,SEQUENCE(r,,r,-1),SEQUENCE(,COLUMNS(d)))
)
)
Upvote
0