Forum Discussion
Solved
sum a value that's spread over a number of years as a fraction
hello, I am trying to work out a formula to sum grants awarded to departments in the example the departments called 5g, gets a grant award for £187,441.84, start date is 01/04/2018 and the end date...
swanjohnny83 Forget that earlier sheet. Try this one with this formula:
=$G2/($D2-$C2+1)*MAX(0,MIN($D2,DATE(K$1+1,8,1)-1)-MAX($C2,DATE(K$1,8,1))+1)It takes the value of the grant divided by the #days in the grant * (lesser of (grant end date or fiscal year end) - greater of (grant start or fiscal yr start) ) and returns 0 if that difference is negative.
As opposed to using a LAMBDA this specifically uses 08-01 as the start of the fiscal year.
I guess why do you even need the year restriction in the formula? why isn't the grant name (5g) enough? Alternatively, you can change the year to be >= 01/04/2018 and another condition <= 31/07/2020.
=SUMIFS($V$2:$V$373,$E$2:$E$373,">="&DATE(2018,4,1),$E$2:$E$373,"<="&DATE(2020,7,31),$AB$2:$AB$373,$K$385,P2:P373,$I$386)
=SUMIFS($V$2:$V$373,$E$2:$E$373,">="&DATE(2018,4,1),$E$2:$E$373,"<="&DATE(2020,7,31),$AB$2:$AB$373,$K$385,P2:P373,$I$386)
- I need to work out how much each department has made between 2018 -2023 for and the grant is awarded for a number of years for example a grant is awarded for 5 years for 3 million it starts at april 2018 and finishes in April 2023 , there about 373 rows and i need to sum up how much has been made in each year, as there are multiple project running for different departments, I need to produce stats on how much each department has made in each year and whether the grant is partner or non partner
- sounds like you should use a Pivot Table.
but as for the "problem" you are having I don't understand. the formula you gave does the sum for a year but then you said, "However this isn't displaying accurate data as the grants can take place for project that are over several years, like the example above". So i gave a solution how to sum over a range of time (multiple years). Now you say you need it for each year.
Maybe a sample spreadsheet showing what you need would help.- so basically the formula, I had used a helper column next to award date for the award year and used the formula based on award year between 2018 to 2023. because there are multiple projects running at different times for example one project starts in April 2018 finished in July 2020 that amount award is £187,414 so that award would be split, i used year frac to workout the fraction of the year which works out at 2.33 so it work out like 80k for 2018, 80k for 2019 and 27k for 2020, so the amount is apportioned , however another project starts in April 2018 and finishes in in march 2019. another starts in January 2020 and finishes in march 2023 . would a pivot table work best for this. I was looking at using a formula to say if the fraction is less than 1 it would just use the calculation above or if the fraction is greater than 1. for the example of January 2020 to march 2023 the fraction is 3.25 , however there are some short projects that are only between April 2019 to June 2019 so it would still use the award year . I can load a sample sheet if that helps
