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 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.
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
- so a Pivot table will do calculations like sum and count and such broken down by catagories and groupings you decide like departments, grants, and dates (which can be grouped as months, quarters, years, etc...). But if you don't know or maybe don't care when the actual expense happened and you just want it spread 'evenly' across the duration of the period then you want the total sum (i believe that would be the formula above or maybe without the year?), divide by the year fraction and then multiple by the year fraction(s) for each applicable year. So if it was from April - June in the same year that would be 0.25 but 0.25/0.25 is 1 so the whole amount is in that year. If it was 2022-10 / 2023-03 that is year fraction 0.5 but 0.25 in 2022 and 0.25 in 2023 so 0.25 / 0.5 is 0.5 in each year. So is that what you need?
- not exactly I need to know which department got grant types either partner or non-partner grants, the amount. if the grant was for 3 years it needs to be apportioned . so I need to know how much of the grant was were for 2018-2023 and total them. I had used helper columns for award year in that formula and grant type either partner or non partner. there are 6 different departments with different grants in the last 5 years. as the information is looking like they are only apply for grants an odd year or so because the grants are spread across multiple years . going back that example between march 2018 to July 2020 the grant was 187k. I used year frac to workout the time between the start date and end date which is 2.33 . so for 2018 is would work out as 80447.14 same amount for 2019 and for 2020 it would be 26547.84 so I need some way to work out a formula that can calculate this , but if the fraction is less then 1 then it would just fall to the original formula that calculates it based on award year as it's less than a year ,example a project runs from march 2019 to June 2019 so I don't need that to be split so its less than a year so the award year would still be 2019 . *note i'm on annual leave now and not back til 14th august and I won't have access to my laptop til then