Forum Discussion
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 is 31/07/2020 ,I managed to use yearfrac to get this as a fraction of 2.33 so the total would be split 2018, 2019 and 2020 .
Previously I've used the formula =SUMIFS($V$2:$V$373,$E$2:$E$373,J386,$AB$2:$AB$373,$K$385,P2:P373,$I$386)
Column V is the where the total income is, Column E is the year , cell J386 in this formula has 2018 so it would find 2018 in Column E2:E373, Column AB2:AB2:AB373 has the name of the cluster/department, column K385 has the name of the cluster I need to find which would be 5g and advanced comms, this is then split by category of either partner or non-industry in column P2:P373, using cell I386 which has Partner.
However this isn't displaying accurate data as the grants can take place for project that are over several years, like the example above
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.
15 Replies
- 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)- 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.
