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

 

[hagia_sofia]

Hello, maybe something like:

=(MAX(MIN(DATE(--RIGHT(D$1,4),7,31),$B2)-MAX(DATE(--LEFT(D$1,4),8,1),$A2)+1,0))/($B2-$A2+1)*$C2

But this takes into account that some years have 366 days which is not what you are looking for?

 

[Kaper]

As you are using Microsoft 365 you can also use new spill formulas, so in D2 write:

=LET(SoAY,DATE(--LEFT(D$1:L$1,4),8,1),EoAY,DATE(--RIGHT(D$1:L$1,4),7,31),DaysStep1,IF(B2<EoAY,B2,EoAY)-IF(A2>SoAY,A2,SoAY)+1,PrDays,IF(DaysStep1>0,DaysStep1,0),PrDays/SUM(PrDays)*C2)

This one is "leap year aware" and in leap years a bit more money is assigned.

If you want to have just in parts of years (thus second example will have 3 "inner" years with exactly 25 000 (BTW - the values on screenshot are obviously wrong for second grant) you may use in D2:

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

And copy the formula down.

The "mechanism behind" is anyway the same as in @hagia_sofia "non-spill" formula.

The formulas are longer, because MIN and MAX formulas try to use array argument as a whole and produce single value, not an array at output .

 

[jswan83]

Hello, maybe something like:

=(MAX(MIN(DATE(--RIGHT(D$1,4),7,31),$B2)-MAX(DATE(--LEFT(D$1,4),8,1),$A2)+1,0))/($B2-$A2+1)*$C2

But this takes into account that some years have 366 days which is not what you are looking for?

Hi,
This works, thanks. so this formula will be aware of leap years. as I've worked out 01/08/20?? to 31/07/20?? is roughly 364 days. I didn't consider the leap years.

 

[jswan83]

As you are using Microsoft 365 you can also use new spill formulas, so in D2 write:

=LET(SoAY,DATE(--LEFT(D$1:L$1,4),8,1),EoAY,DATE(--RIGHT(D$1:L$1,4),7,31),DaysStep1,IF(B2<EoAY,B2,EoAY)-IF(A2>SoAY,A2,SoAY)+1,PrDays,IF(DaysStep1>0,DaysStep1,0),PrDays/SUM(PrDays)*C2)

This one is "leap year aware" and in leap years a bit more money is assigned.

If you want to have just in parts of years (thus second example will have 3 "inner" years with exactly 25 000 (BTW - the values on screenshot are obviously wrong for second grant) you may use in D2:

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

And copy the formula down.

The "mechanism behind" is anyway the same as in @hagia_sofia "non-spill" formula.

The formulas are longer, because MIN and MAX formulas try to use array argument as a whole and produce single value, not an array at output .

Hi Kaper,
thanks, It appears, I have a lot still to learn about excel, i don't really know the spill formula, do I just drag the formula down rows in order for it fill in the rows?

 

[Kaper]

01/08/20?? to 31/07/20?? is either 365 or 366 days. if you calculate period length in days you take endday-startday+1
Easy way to understand this +1 is if you imagine 1 day project . If you jyst take endday-startday you will have 0. so +1 is needed

A spill formula - it spills automatically (in this case - to the right, the same number of columns ans nymber of AY header in row1 ).
so horizontal filling of table is taken by formula, but it has to be copied to next rows (cells D3, D4, ...) to have also vertical fill.

 

[jswan83]

01/08/20?? to 31/07/20?? is either 365 or 366 days. if you calculate period length in days you take endday-startday+1
Easy way to understand this +1 is if you imagine 1 day project . If you jyst take endday-startday you will have 0. so +1 is needed

A spill formula - it spills automatically (in this case - to the right, the same number of columns ans nymber of AY header in row1 ).
so horizontal filling of table is taken by formula, but it has to be copied to next rows (cells D3, D4, ...) to have also vertical fill.

Hi Kaper,
Thanks for clarifying, I've just tried this formula, however, in the cells where there's zeros, it just has the the #spill! error, can I add to it, to display £0 instead of the #spill! error. added an image.

 

[Kaper]

delete any zeros or old values from D2: before inserting formula. And insert it only in D2 then copy down only (no right copying even if xlBB shows D2:L4 the formula is copied only to D2:D4 and is spilled to the right)

Zeszyt1
	A	B	C	D	E	F	G	H	I	J	K	L
1	start	end	amount	2016/2017	2017/2018	2018/2019	2019/2020	2020/2021	2021/2022	2022/2023	2023/2024	2024/2025
2	01.08.2017	30.11.2020	587 009,00 GBP	0,00 GBP	175 909,92 GBP	175 909,92 GBP	176 391,87 GBP	58 797,29 GBP	0,00 GBP	0,00 GBP	0,00 GBP	0,00 GBP
3	01.04.2017	31.03.2021	100 000,00 GBP	8 350,44 GBP	24 982,89 GBP	24 982,89 GBP	25 051,33 GBP	16 632,44 GBP	0,00 GBP	0,00 GBP	0,00 GBP	0,00 GBP
4	01.08.2017	30.11.2020	239 944,00 GBP	0,00 GBP	71 904,40 GBP	71 904,40 GBP	72 101,40 GBP	24 033,80 GBP	0,00 GBP	0,00 GBP	0,00 GBP	0,00 GBP
Arkusz1

Cell Formulas
Range	Formula
D2:L4	D2	=LET(SoAY,DATE(--LEFT(D$1:L$1,4),8,1),EoAY,DATE(--RIGHT(D$1:L$1,4),7,31),DaysStep1,IF(B2<EoAY,B2,EoAY)-IF(A2>SoAY,A2,SoAY)+1,PrDays,IF(DaysStep1>0,DaysStep1,0),PrDays/SUM(PrDays)*C2)
Dynamic array formulas.

 

[Kaper]

Just one more step - it is really one formula in one cell (D2) spilled right and down. so empty all D2:L4 and insert in d2 (of course you will have yo apply currency formatting to all output cells) :

=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,SUM)*C2:C4)

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	start	end	amount	2016/2017	2017/2018	2018/2019	2019/2020	2020/2021	2021/2022	2022/2023	2023/2024	2024/2025
2	01.08.2017	30.11.2020	587009	0	176054,5	176054,5	176054,5	58845,6	0	0	0	0
3	01.04.2017	31.03.2021	100000	8356,16438	25000	25000	25000	16643,84	0	0	0	0
4	01.08.2017	30.11.2020	239944	0	71963,48	71963,48	71963,48	24053,55	0	0	0	0
Arkusz1

Cell Formulas
Range	Formula
D2:L4	D2	=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,SUM)*C2:C4)
Dynamic array formulas.

 

[jswan83]

Just one more step - it is really one formula in one cell (D2) spilled right and down. so empty all D2:L4 and insert in d2 (of course you will have yo apply currency formatting to all output cells) :

=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,SUM)*C2:C4)

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	start	end	amount	2016/2017	2017/2018	2018/2019	2019/2020	2020/2021	2021/2022	2022/2023	2023/2024	2024/2025
2	01.08.2017	30.11.2020	587009	0	176054,5	176054,5	176054,5	58845,6	0	0	0	0
3	01.04.2017	31.03.2021	100000	8356,16438	25000	25000	25000	16643,84	0	0	0	0
4	01.08.2017	30.11.2020	239944	0	71963,48	71963,48	71963,48	24053,55	0	0	0	0
Arkusz1

Cell Formulas
Range	Formula
D2:L4	D2	=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,SUM)*C2:C4)
Dynamic array formulas.

Hi Kaper,
I tried the newer formula, however I'm getting the #Name! error, the previous formula works, after removing all the figures. if there a difference between formulas?