SOLVED

# Is there a way to combine BYROW&BYCOL to make my formula SPILL across & down with one cell formula?

Copper Contributor

# Is there a way to combine BYROW&BYCOL to make my formula SPILL across & down with one cell formula?

I have done it BYCOL and BYROW individually but am struggling to make it one...

BYCOL:

BYROW:

Can anyone help make this one dynamic / spilled formula in the first cell?

I've tried nesting BYCOL and BYROW together and have tried using MAKEARRAY but am struggling to get a result.

(The names are made up for testing the formula).

3 Replies

# Re: Is there a way to combine BYROW&BYCOL to make my formula SPILL across & down with one ce

=DROP(REDUCE("",SEQUENCE(ROWS(C38:D40)),LAMBDA(a,b,VSTACK(a,BYCOL(E37:P37,LAMBDA(months,MAX(0,NETWORKDAYS(MAX(EOMONTH(months,-1)+1,INDEX(C38:C40,b)),MIN(INDEX(D38:D40,b),EOMONTH(months,0))))))))),1)

With REDUCE and LAMBDA you can spill the results horizontally and vertically. The results are not the same as in your sheet but this is most likely because of the entries in BH_Table[Date].

best response confirmed by WilliamBaggs (Copper Contributor)
Solution

# Re: Is there a way to combine BYROW&BYCOL to make my formula SPILL across & down with one ce

=MAKEARRAY(ROWS(C38:C40),COLUMNS(E37:P37),LAMBDA(r,c,MAX(0,NETWORKDAYS(MAX(EOMONTH(INDEX(E37:P37,c),-1)+1,INDEX(C38:C40,r)),MIN(INDEX(D38:D40,r),EOMONTH(INDEX(E37:P37,c),0))))))

Following your suggestion MAKEARRAY and LAMBDA could be an alternative.

# Re: Is there a way to combine BYROW&BYCOL to make my formula SPILL across & down with one ce

Thanks, Oliver - both are perfect. Really appreciate your help.

The BH_Table is just a list of Bank Holidays. So I've amended to include that and all is perfect and as expected.
1 best response

Accepted Solutions
best response confirmed by WilliamBaggs (Copper Contributor)
Solution

# Re: Is there a way to combine BYROW&BYCOL to make my formula SPILL across & down with one ce

=MAKEARRAY(ROWS(C38:C40),COLUMNS(E37:P37),LAMBDA(r,c,MAX(0,NETWORKDAYS(MAX(EOMONTH(INDEX(E37:P37,c),-1)+1,INDEX(C38:C40,r)),MIN(INDEX(D38:D40,r),EOMONTH(INDEX(E37:P37,c),0))))))

Following your suggestion MAKEARRAY and LAMBDA could be an alternative.