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