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)))

 

[awoohaw]

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
View attachment 107405

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.

Are you saying you want to return a blank if columns V and AA are missing data, ... what what the addition statement in your original post about?
never mind

 

[ScottTemple]

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

My mistake Fluff, I thought you indicated to put that in A2 and not AX2. That does work. However, I will not some more assistance as I have three more formulas that are similar to that one, but I'm not sure how to interpret this formula you provided to update my other three formulas.

=IF(INDEX(A2#,,13),(INDEX(A2#,,13)-((INDEX(A2#,,23)+INDEX(A2#,,24)+(INDEX(A2#,,28)+(INDEX(A2#,,29)))))))
=IF(INDEX(A2#,,12),(AY2#)/(INDEX(A2#,,12)))
=IF(AY2#="","",IF(INDEX(A2#,,13)="","",1-(AY2#)/(INDEX(A2#,,13))))

Akin to teaching a man to fish, I would love to know how I can incorporate the formula you provided so I can modify my last three, in the event I need to add more in the future.

 

[ScottTemple]

My mistake Fluff, I thought you indicated to put that in A2 and not AX2. That does work. However, I will not some more assistance as I have three more formulas that are similar to that one, but I'm not sure how to interpret this formula you provided to update my other three formulas.

=IF(INDEX(A2#,,13),(INDEX(A2#,,13)-((INDEX(A2#,,23)+INDEX(A2#,,24)+(INDEX(A2#,,28)+(INDEX(A2#,,29)))))))
=IF(INDEX(A2#,,12),(AY2#)/(INDEX(A2#,,12)))
=IF(AY2#="","",IF(INDEX(A2#,,13)="","",1-(AY2#)/(INDEX(A2#,,13))))

Akin to teaching a man to fish, I would love to know how I can incorporate the formula you provided so I can modify my last three, in the event I need to add more in the future.

Oh shoot, think I may have figured it out.

=IF(INDEX(A2#,,13),(INDEX(A2#,,13)-((INDEX(A2#,,23)+INDEX(A2#,,24)+(INDEX(A2#,,28)+(INDEX(A2#,,29)))))))

is

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

 

[ScottTemple]

Oh shoot, think I may have figured it out.

=IF(INDEX(A2#,,13),(INDEX(A2#,,13)-((INDEX(A2#,,23)+INDEX(A2#,,24)+(INDEX(A2#,,28)+(INDEX(A2#,,29)))))))

is

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

That gives me a #VALUE! error though if A2#,,13 is blank. Any thoughts?

I tried the below, but I still get a value error

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

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks