trying to split grant award across academic years

J

jswan83

New Member
Joined
Nov 19, 2024
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • example1.PNG
    example1.PNG
    44.4 KB · Views: 12
Sort by date Sort by votes
The main difference is that I used BYROW function to calculate each grant total duration. Below is better sample with xlBB. So below is the correct one:

You may analyze the formula steb by step looking at partial results, so you may start (somewhere below like row 10) with:
Excel Formula: 
=LET(AY,--LEFT(D$1:L$1,4),AY)
then watch next step (say in row 15):
Excel Formula: 
=LET(AY,--LEFT(D$1:L$1,4),SoAY,DATE(AY,8,1),SoAY)
(format the results as dates)
and so on.

Example.xlsx
ABCDEFGHIJKL
1startendamount2016/20172017/20182018/20192019/20202020/20212021/20222022/20232023/20242024/2025
201.08.201730.11.2020£587 009,00£0,00£176 054,47£176 054,47£176 054,47£58 845,60£0,00£0,00£0,00£0,00
301.04.201731.03.2021£100 000,00£8 356,16£25 000,00£25 000,00£25 000,00£16 643,84£0,00£0,00£0,00£0,00
401.08.201730.11.2020£239 944,00£0,00£71 963,48£71 963,48£71 963,48£24 053,55£0,00£0,00£0,00£0,00
Arkusz1
Cell Formulas
RangeFormula
D2:L4D2=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.
 
Upvote 0
Solution

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Kaper said:
The main difference is that I used BYROW function to calculate each grant total duration. Below is better sample with xlBB. So below is the correct one:

You may analyze the formula steb by step looking at partial results, so you may start (somewhere below like row 10) with:
Excel Formula: 
=LET(AY,--LEFT(D$1:L$1,4),AY)
then watch next step (say in row 15):
Excel Formula: 
=LET(AY,--LEFT(D$1:L$1,4),SoAY,DATE(AY,8,1),SoAY)
(format the results as dates)
and so on.

Example.xlsx
ABCDEFGHIJKL
1startendamount2016/20172017/20182018/20192019/20202020/20212021/20222022/20232023/20242024/2025
201.08.201730.11.2020£587 009,00£0,00£176 054,47£176 054,47£176 054,47£58 845,60£0,00£0,00£0,00£0,00
301.04.201731.03.2021£100 000,00£8 356,16£25 000,00£25 000,00£25 000,00£16 643,84£0,00£0,00£0,00£0,00
401.08.201730.11.2020£239 944,00£0,00£71 963,48£71 963,48£71 963,48£24 053,55£0,00£0,00£0,00£0,00
Arkusz1
Cell Formulas
RangeFormula
D2:L4D2=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.
Click to expand...
I'm still having trouble getting the formula to work, I tried the error tracing, it seems like excel doesn't like the byrow function . I will try using the add in tonight, when I'm home from work
 

Attachments

  • example3.PNG
    example3.PNG
    19.7 KB · Views: 3
Upvote 0
To test if byrow works fine for you try (in range of some empty cells) like P2:
Excel Formula: 
=BYROW(A2:B4,AVERAGE)
if it does nor return error format cells as date and you shall see date in the middle of each grant. If you get errors, there is indeed some problem with byrow.

You may also try the file which shall be available by link to everybody: Loading Google Sheets
 
Upvote 0
Kaper said:
To test if byrow works fine for you try (in range of some empty cells) like P2:
Excel Formula: 
=BYROW(A2:B4,AVERAGE)
if it does nor return error format cells as date and you shall see date in the middle of each grant. If you get errors, there is indeed some problem with byrow.

You may also try the file which shall be available by link to everybody: Loading Google Sheets
Click to expand...
Hi Kaper,
I removed the formula and tried the byrow formula, in cell p2 and getting the #name? error, can you clarify what should be formatted as date, column A and B?
 
Upvote 0
Kaper said:
The main difference is that I used BYROW function to calculate each grant total duration. Below is better sample with xlBB. So below is the correct one:

You may analyze the formula steb by step looking at partial results, so you may start (somewhere below like row 10) with:
Excel Formula: 
=LET(AY,--LEFT(D$1:L$1,4),AY)
then watch next step (say in row 15):
Excel Formula: 
=LET(AY,--LEFT(D$1:L$1,4),SoAY,DATE(AY,8,1),SoAY)
(format the results as dates)
and so on.

Example.xlsx
ABCDEFGHIJKL
1startendamount2016/20172017/20182018/20192019/20202020/20212021/20222022/20232023/20242024/2025
201.08.201730.11.2020£587 009,00£0,00£176 054,47£176 054,47£176 054,47£58 845,60£0,00£0,00£0,00£0,00
301.04.201731.03.2021£100 000,00£8 356,16£25 000,00£25 000,00£25 000,00£16 643,84£0,00£0,00£0,00£0,00
401.08.201730.11.2020£239 944,00£0,00£71 963,48£71 963,48£71 963,48£24 053,55£0,00£0,00£0,00£0,00
Arkusz1
Cell Formulas
RangeFormula
D2:L4D2=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.
Click to expand...
According to google, byrow, uses a lamda function, would this work =BYROW(range,LAMBDA(row,SUM(row))) or something similar? if not I'll upload the spreadsheet, when i get home
 
Upvote 0
Are you using 365 as per your profile, or some other version?
 
Upvote 0
In that case it sounds as though you have not updated it for sometime, as BYROW has been out a few years.
 
Upvote 0
Fluff said:
In that case it sounds as though you have not updated it for sometime, as BYROW has been out a few years.
Click to expand...
I have just downloaded the update, I tried the =byrow(a2:c4,average) and still getting the #name? error, should the dates in A2:to C$ be formated in short date formula, bare in mind I'm in the uk, just incase I need to change the date format to mm/dd/yyyy?
 
Upvote 0
If you type in =by into a cell what options does the "function picker" show?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
705
Peter_SSs
Peter_SSs
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
566
mrshuck
M
F
  • Solved
Formula determine if a date falls between a specific M/D range for multiple years for YTD comparison.
Replies
6
Views
944
flashgordie
F
k3yn0t3
  • Solved
INDIRECT with INDEX(MATCH())?
2
Replies
10
Views
1K
k3yn0t3
k3yn0t3
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
655
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,812
Messages
6,181,094
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top