Forum Discussion
Solved
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...
=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.
=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].