Mar 15 2024 09:48 AM - edited Mar 15 2024 09:50 AM
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).
Mar 15 2024 11:12 AM
=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].
Mar 15 2024 11:53 AM
Solution=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.
Mar 18 2024 02:45 AM
Mar 15 2024 11:53 AM
Solution=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.