trying to split grant award across academic years

[jswan83]

Hi,
i am trying to come up with a formula to split project grants across, multiple years.
The academic year starts in the 01/08/2016 and finished the 31/07/2017
one example column A2 has a start date of 01/08/2017 and finishes on the 30/11/2020 in column B2.
the total in C2 is £587,009. in columns d2-G2 has the academic years 2017/2018-2020/2021.

The expected answers have been entered in columns D:H.

I have taken a screenshot, if necessary I can upload the spreadsheet, however I'll need to wait until I am home, as my work computer doesn't allow me to download the add in for XL2BB.

Thanks

Johnny

 

[jswan83]

There are 2 options bycol and byrow, when I type in =by, think the issue is the 2nd part of byrow function as it's =byrow(range,function).
unless, I'm overlooking something simple

 

[Fluff]

If you're on the semi annual channel you won't have the Eta reduced lambda functions yet.
Try it like

=LET(AY,--LEFT(D$1:L$1,4),SoAY,DATE(AY,8,1),EoAY,DATE(AY+1,7,31),DaysStep1,IF(B2:B4<EoAY,B2:B4,EoAY)-IF(A2:A4>SoAY,A2:A4,SoAY)+1,PrPart,IF(DaysStep1>0,DaysStep1,0)/(DATE(AY+1,12,31)-DATE(AY,12,31)),PrPart/BYROW(PrPart,LAMBDA(br,SUM(br)))*C2:C4)

 

[jswan83]

If you're on the semi annual channel you won't have the Eta reduced lambda functions yet.
Try it like

=LET(AY,--LEFT(D$1:L$1,4),SoAY,DATE(AY,8,1),EoAY,DATE(AY+1,7,31),DaysStep1,IF(B2:B4<EoAY,B2:B4,EoAY)-IF(A2:A4>SoAY,A2:A4,SoAY)+1,PrPart,IF(DaysStep1>0,DaysStep1,0)/(DATE(AY+1,12,31)-DATE(AY,12,31)),PrPart/BYROW(PrPart,LAMBDA(br,SUM(br)))*C2:C4)

Thanks Fluff, you solved it , this has been giving me migraines, thank you. and also thanks to kaper. so glad, clearly I need to learn the Let/Lamba formulas.
I had a feeling, I had to use lamda with the byrows formula

 

[Fluff]

Glad we could help & thanks for the feedback.