CHOOSECOLS, BYROW, LET, SPILL ARRAY, Return Blank Value

[ScottTemple]

Hello,

I have the following formula:

=CHOOSECOLS(A2#,13)-BYROW(CHOOSECOLS(A2#,23,24,28,29),LAMBDA(br,SUM(br)))
However, if the value in A2#,13 is missing, I receive a #VALUE! error. I've made attempts to correct this, but the best formula I could come up with does not work (see below). Any thoughts?

=LET(a,CHOOSECOLS(A2#,13)-BYROW(CHOOSECOLS(A2#,23,24,28,29),LAMBDA(br,SUM(br))),IF(a="","",a))

 

[awoohaw]

try this:

=LET(
a,CHOOSECOLS(A2#,13),
b,IF(a="","",a),
c,CHOOSECOLS(A2#,23,24,28,29),
d,BYROW(c,LAMBDA(c,SUM(c))),
a-d)

 

[Peter_SSs]

if the value in A2#,13 is missing,

In that circumstance, do you want to treat the missing value as zero? If not, what calculation should occur?
Assuming the zero option, see if this works for you.

=N(CHOOSECOLS(A2#,13))-BYROW(CHOOSECOLS(A2#,23,24,28,29),LAMBDA(br,SUM(br)))

 

[ScottTemple]

Thanks Peter, that did the trick.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.