ACONSTANT

[schardt679]

ACONSTANT returns an array of value(s) repeated over a specified number of rows and columns.

Thanks to GeertD's CONSTANTARRAY function for the inspiration.
Single values repeat down rows & across columns. Horizontal values repeat down rows. Vertical values repeat across columns.
If nRows or nCols have more than 1 value, the max number is used.
Optional arguments require comma to work. Calls AONEDIM & CELLCOUNT.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ACONSTANT([Value]☛ value to repeat; ="", [nRows]☛ number of rows to repeat value; =1, [nCols]☛ number of columns to repeat value; =1) ⁂[]=optional: use ","; =default; =omit

=LAMBDA(Value,nRows,nCols,
      LET(Val, Value,          nRow, MAX(N(IFERROR(nRows,))),          nCol, MAX(N(IFERROR(nCols,))),
             A1D?, AONEDIM(Val),          AScan, AND(A1D?<>{"H","V"}),
             NumScan, OR(nRow<0, nCol<0),          NumMSG, "nRows/nCols>=1",
             ERRORS, IFS(AScan, A1D?,  NumScan, NumMSG,  1, 0),
             RowCt, CELLCOUNT(Val, 1),          RowTot, IFS(RowCt>1, RowCt,  nRow=0, 1,  1, nRow),
             ColCt, CELLCOUNT(Val, 2),          ColTot, IFS(ColCt>1, ColCt,  nCol=0, 1,  1, nCol),
             RowSeq, SEQUENCE(RowTot,,, IF(RowCt>1, 1, 0)),          ColSeq, SEQUENCE(, ColTot,, IF(ColCt>1, 1, 0)),
             Result, INDEX(IF(Val="", "", Val), RowSeq, ColSeq),
             Return, IF(ERRORS<>0, ERRORS, Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		ACONSTANT
2
3		Value		nRows	nCols			Result
4		3.14		3	3			3.14	3.14	3.14
5								3.14	3.14	3.14
6								3.14	3.14	3.14
7
8		Value is a single cell
9		Value is repeated down 3 rows and across 3 columns
10		Formula in cell H4☛ =ACONSTANT(B4, D4, E4)
11
12
13
14		Value		nRows	nCols			Result
15		A		3	4			A	A	A	A
16		B						B	B	B	B
17
18		Value array is a vertical array
19		Values are repeated across 4 columns
20		Formula in cell H15☛ =ACONSTANT(B15:B16, D15, E15)
21
22
23
24		Value		nRows	nCols			Result
25		Item 1, Item 2	Item 3, Item 4	3	4			Item 1, Item 2	Item 3, Item 4
26								Item 1, Item 2	Item 3, Item 4
27								Item 1, Item 2	Item 3, Item 4
28
29
30		Value array is a horizontal array
31		Values are repeated down 3 rows
32		Formula in cell H25☛ =ACONSTANT(B25:C25, D25, E25)
33
34
35
36		Value		nRows	nCols			Result
37		07/08/21		2				07/08/21
38								07/08/21
39
40		Value is a single cell
41		Value is repeated 2 times down rows and once across columns
42		Formula in cell H37☛ =ACONSTANT(B37, D37, E37)
43
44
45
46		Value		nRows	nCols			Result
47				3	3
48
49
50
51		Value is single cell
52		Value is repeated down 3 rows and across 3 columns
53		Formula in cell H47☛ =ACONSTANT(B47, D47, E47)
54
55
56
57		Value		nRows	nCols			Result
58		#N/A		1	1	2		#N/A	#N/A
59				2				#N/A	#N/A
60				3				#N/A	#N/A
61				4				#N/A	#N/A
62		Value is single cell
63		Value is repeated down 4 rows and across 2 columns
64		Formula in cell H58☛ =ACONSTANT(B58, D58:D61, E58:F58)
65
ACONSTANT

Cell Formulas
Range	Formula
H4:J6,H47:J49,H37:H38	H4	=ACONSTANT(B4, D4, E4)
B10,B64,B53	B10	=AFORMULATEXT(H4)
H15:K16	H15	=ACONSTANT(B15:B16, D15, E15)
B20,B42	B20	=AFORMULATEXT(H15)
H25:I27	H25	=ACONSTANT(B25:C25, D25, E25)
B32	B32	=AFORMULATEXT(H25)
H58:I61	H58	=ACONSTANT(B58, D58:D61, E58:F58)
Dynamic array formulas.

 

[schardt679]

ACONSTANT has been rewritten. It is shorter, takes advantage of the optional argument syntax, and values can now be repeated in a looped manner.

ACONSTANT returns an array of value(s) repeated over a specified number of rows and columns.

Thanks to GeertD's CONSTANTARRAY function for the inspiration.
If nRows or nCols have more than 1 value, the max number is used.
If nRows/ nCols are greater than Array RowCt / ColCt, it loops back the top/ left value of the Array.
If nRows/ nCols are less than Array RowCt / ColCt, it repeats up to that row/ column of the Array.
If skipping arguments, a comma is required. Calls IFBLANK & CELLCOUNT.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ACONSTANT([Value]☛ value to repeat; ="", [nRows]☛ number of rows to repeat value; =1, [nCols]☛ number of columns to repeat value; =1) ⁂[]=optional; =default; =omit

The syntax is the same: ACONSTANT([Value],[nRows],[nCols])

The arguments are the same nut it now repeats the Value argument differently.
1. Single values still repeat across rows and columns.
2. If nRows or nCols is greater than the Value Array row or column count, the values are looped until that number is reached.
3. If nRows or nCols is less than the Value Array row or column count, the values are indexed to that row or column number.
[Value]: Optional. Value to repeat; ignored☛ empty string.
[nRows]: Optional. Number of times to repeat value down rows; ignored☛ 1.
[nCols]: Optional. Number of times to repeat value across columns; ignored☛ 1.

=LAMBDA([Value],[nRows],[nCols],
      LET(Val, IFBLANK(Value, ""),          nRow, IFERROR(--(nRows),),          nCol, IFERROR(--(nCols),),
             NumScan, OR(nRow<0, nCol<0),          NumMSG, "nRows/nCols>=1",
             RowCt, CELLCOUNT(Val, 1),          RowSeq, MOD(SEQUENCE(MAX(nRow, 1),, 0), RowCt)+1,
             ColCt, CELLCOUNT(Val, 2),          ColSeq, MOD(SEQUENCE(, MAX(nCol, 1), 0), ColCt)+1,
             Result, INDEX(Val, RowSeq, ColSeq),
             Return, IF(NumScan, NumMSG,  Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		ACONSTANT
2
3		Value			nRows	nCols		Result
4		3.14			3	3		3.14	3.14	3.14
5								3.14	3.14	3.14
6								3.14	3.14	3.14
7
8		Value is a single cell
9		Value is repeated down 3 rows and across 3 columns
10		Formula in cell H4☛ =ACONSTANT(B4, E4, F4)
11
12
13
14		Value			nRows	nCols		Result
15		A			3	4		A	A	A	A
16		B						B	B	B	B
17								A	A	A	A
18		Value array is a vertical array
19		Value is repeated down 3 rows and across 4 columns
20		Formula in cell H15☛ =ACONSTANT(B15:B16, E15, F15)
21
22
23
24		Value			nRows	nCols		Result
25		Item 1, Item 2	Item 3, Item 4		4	3		Item 1, Item 2	Item 3, Item 4	Item 1, Item 2
26								Item 1, Item 2	Item 3, Item 4	Item 1, Item 2
27								Item 1, Item 2	Item 3, Item 4	Item 1, Item 2
28								Item 1, Item 2	Item 3, Item 4	Item 1, Item 2
29
30		Value array is a horizontal array
31		Value is repeated down 4 rows and across 3 columns
32		Formula in cell H25☛ =ACONSTANT(B25:C25, E25, F25)
33
34
35
36		Value			nRows	nCols		Result
37		Item 1, Item 2	Item 3, Item 4		3	3		Item 1, Item 2	Item 3, Item 4	Item 1, Item 2
38		Item 5, Item 6	Item 7, Item 8					Item 5, Item 6	Item 7, Item 8	Item 5, Item 6
39								Item 1, Item 2	Item 3, Item 4	Item 1, Item 2
40
41
42		Value array is a 2D array
43		Value is repeated down 3 rows and across 3 columns
44		Formula in cell H37☛ =ACONSTANT(B37:C38, E37, F37)
45
46
47
48
49
50
51		Value			nRows	nCols		Result
52		08/08/21			2	3		08/08/21	08/08/21	08/08/21
53		08/09/21						08/09/21	08/09/21	08/09/21
54		08/10/21
55
56		Value array is a vertical array
57		Value is repeated down 2 rows and across 3 columns
58		Formula in cell H52☛ =ACONSTANT(B52:B54, E52, F52)
59
60
61
62		Value			nRows	nCols		Result
63					3	3
64
65
66
67		Value is single cell
68		Value is repeated down 3 rows and across 3 columns
69		Formula in cell H63☛ =ACONSTANT(B63:C64, E63, F63)
70
71
72
73		Value			nRows	nCols		Result
74		#N/A			1	1		#N/A	#N/A
75					2	2		#N/A	#N/A
76					3			#N/A	#N/A
77					4			#N/A	#N/A
78		Value is single cell
79		Value is repeated down 4 rows and across 2 columns
80		Formula in cell H74☛ =ACONSTANT(B74, E74:E77, TRANSPOSE(F74:F75))
81
ACONSTANT

Cell Formulas
Range	Formula
H4:J6	H4	=ACONSTANT(B4, E4, F4)
B10,B80,B69,B58	B10	=AFORMULATEXT(H4)
H15:K17	H15	=ACONSTANT(B15:B16, E15, F15)
B20	B20	=AFORMULATEXT(H15)
H25:J28	H25	=ACONSTANT(B25:C25, E25, F25)
B32,B44	B32	=AFORMULATEXT(H25)
H37:J39,H63:J65	H37	=ACONSTANT(B37:C38, E37, F37)
H52:J53	H52	=ACONSTANT(B52:B54, E52, F52)
H74:I77	H74	=ACONSTANT(B74, E74:E77, TRANSPOSE(F74:F75))
Dynamic array formulas.