IF(OR(ISBLANK Formula that is part of a Spill Array

[ScottTemple]

Hello,

I have a spill array in tab2 of my workbook as follows:

=LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(30)),IF(a="","",a))

I have a formula in cell AX:

=(INDEX(A2#,,22)+(INDEX(A2#,,27)))

However, if either 22 or 27 is missing it's value/blank, I need to have the calculation still continue, currently it returns a #VALUE! error in the cell. I have attempted the following formula, but receive an error message:

=IF(OR(ISBLANK(INDEX(A2#,,22),ISBLANK(INDEX(A2#,,27)),"",(INDEX(A2#,,22)+(INDEX(A2#,,27)))

 

[ScottTemple]

small correction:
=LET(a,$A$2:$AD$6,aa,IF(a="",0,a),BYROW(aa,LAMBDA(aa,SUM(aa)))) unless the array is already defined in your worksheet:

=LET(a,$A$2:$AD$6,aa,IF(a="",0,a),BYROW(aa,LAMBDA(aa,SUM(aa))))

Cell A2 contains my spill array formula though: =LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(30)),IF(a="","",a))

 

[awoohaw]

Cell A2 contains my spill array formula though: =LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(30)),IF(a="","",a))

then this:

=LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(,30)),BYROW(a,LAMBDA(a,SUM(a))))

 

[Fluff]

How about

=BYROW(CHOOSECOLS(A2#,22,27),LAMBDA(br,SUM(br)))

 

[ScottTemple]

then this:

=LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(,30)),aa,IF(a="",0,a),BYROW(aa,LAMBDA(aa,SUM(aa))))

That gives me a circle reference warning and then wipes out my spill data. I don't believe changing the CHOOSECOLS is the right path. I have other formulas in columns AE to BA that pull from my spill range and they have different formulas for calculating data. Does the ISBLANK with OR not work with spill arrays?

 

[ScottTemple]

How about

=BYROW(CHOOSECOLS(A2#,22,27),LAMBDA(br,SUM(br)))

Same scenario Fluff. I get the same error code and then my spill array disappaers.

 

[awoohaw]

That gives me a circle reference warning and then wipes out my spill data. I don't believe changing the CHOOSECOLS is the right path. I have other formulas in columns AE to BA that pull from my spill range and they have different formulas for calculating data. Does the ISBLANK with OR not wor

this?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE																			AX
1
2	2	1	3	9	7	8	1	3	5	5	1	10	8	2	4	3	2	6	5	6	10	4	8	2	4	2	10	6	9	5																				151
3	2	6	5		4	3	6	5	1	5	3	7	2	5	3	8	3	6	1	7	6	7	3	10	7	3	8	2	1	6																				135
4	9	10	6	1	1		10	8	6	9	4		1		10	5	8	8	8	6	2	5	10	6	1	3	5	6	4	3																				155
5	1	2	2	10	7	10	5	5		4	8	2	1	9	1	8	1	8	9	4	2	4	10	5	5	10	2	5	3	3																				146
6	3	8	6	1	5	5	1	4	7	10	6	4	2	5	2	3	10	10	5	1	9	9	1	7	2	5	10	8	5	2																				156
7
8
9
10	2	1	3	9	7	8	1	3	5	5	1	10	8	2	4	3	2	6	5	6	10	4	8	2	4	2	10	6	9	5
11	2	6	5		4	3	6	5	1	5	3	7	2	5	3	8	3	6	1	7	6	7	3	10	7	3	8	2	1	6
12	9	10	6	1	1		10	8	6	9	4		1		10	5	8	8	8	6	2	5	10	6	1	3	5	6	4	3
13	1	2	2	10	7	10	5	5		4	8	2	1	9	1	8	1	8	9	4	2	4	10	5	5	10	2	5	3	3
14	3	8	6	1	5	5	1	4	7	10	6	4	2	5	2	3	10	10	5	1	9	9	1	7	2	5	10	8	5	2
Sheet1

Cell Formulas
Range	Formula
A2:AD6	A2	=LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(30)),IF(a="","",a))
AX2:AX6	AX2	=LET(a,$A$2#,BYROW(a,LAMBDA(a,SUM(a))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Shaver_Data	=Sheet1!$A$10:$AD$14	A2

 

[ScottTemple]

this?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE																			AX
1
2	2	1	3	9	7	8	1	3	5	5	1	10	8	2	4	3	2	6	5	6	10	4	8	2	4	2	10	6	9	5																				151
3	2	6	5		4	3	6	5	1	5	3	7	2	5	3	8	3	6	1	7	6	7	3	10	7	3	8	2	1	6																				135
4	9	10	6	1	1		10	8	6	9	4		1		10	5	8	8	8	6	2	5	10	6	1	3	5	6	4	3																				155
5	1	2	2	10	7	10	5	5		4	8	2	1	9	1	8	1	8	9	4	2	4	10	5	5	10	2	5	3	3																				146
6	3	8	6	1	5	5	1	4	7	10	6	4	2	5	2	3	10	10	5	1	9	9	1	7	2	5	10	8	5	2																				156
7
8
9
10	2	1	3	9	7	8	1	3	5	5	1	10	8	2	4	3	2	6	5	6	10	4	8	2	4	2	10	6	9	5
11	2	6	5		4	3	6	5	1	5	3	7	2	5	3	8	3	6	1	7	6	7	3	10	7	3	8	2	1	6
12	9	10	6	1	1		10	8	6	9	4		1		10	5	8	8	8	6	2	5	10	6	1	3	5	6	4	3
13	1	2	2	10	7	10	5	5		4	8	2	1	9	1	8	1	8	9	4	2	4	10	5	5	10	2	5	3	3
14	3	8	6	1	5	5	1	4	7	10	6	4	2	5	2	3	10	10	5	1	9	9	1	7	2	5	10	8	5	2
Sheet1

Cell Formulas
Range	Formula
A2:AD6	A2	=LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(30)),IF(a="","",a))
AX2:AX6	AX2	=LET(a,$A$2#,BYROW(a,LAMBDA(a,SUM(a))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Shaver_Data	=Sheet1!$A$10:$AD$14	A2

No unfortunately that doesn't work either.

 

[Fluff]

Which column are you trying to put the formula in?

 

[ScottTemple]

Which column are you trying to put the formula in?

Column AX. Columns A - AD contain the spill array, and then I have various formulas that pull from that array in columns AE - BA.

V= Index A2#,,22 AA= Index A2#,,27

AX2 should return a blank value, as there is no data in V or AA for that row.
AX28 should return a value of 8 as there is an 8 in V and nothing in AA.

 

[Fluff]

If this =(INDEX(A2#,,22)+(INDEX(A2#,,27))) works other than the errors, then I see no reason why

=BYROW(CHOOSECOLS(A2#,22,27),LAMBDA(br,SUM(br)))

wouldn't work if placed in col AX