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.
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
- I'm sorry I'm just not getting it. I see 2 ways of recording costs and the first is using Actuals so based on actual date of transactions and summing for that actual date range. The second is usually more for projections and assuming equal spread so if the range was 2018-03 through 2020-07 you could estimate 10 months in 2018, 12 months in 2019 and 7 months in 2020 (note you have to define the end points as inclusive or exclusive as in do you include March when it starts and do you include July when it ends?). But I don't see how either equate to what you say:
"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"
I can see year frac of 2.33 (i.e. mid-march to mid-July is 4 months plus the 2 years in between is 2.333) but I don't see how you use that to then divide it into those values for 2018,2019, and 2020. If the dates had been Jan 2018 - Apr 2020 then 2018 and 2019 are full years and 2020 is 1/3 year and then that would make sense.- Hello,
I'm just back from annual leave.
Its mainly because I need to work out it by year for each department. the number of months shouldn't really matter I'm using the start date and end date as a fraction. so I would need to split the grants by the time between projects , previously I had worked out in months but I got told it was too much detail. this was also using the date when the grant was award to sum up each department but it was looking like there was no income for several months/ years. I can create a mock spreadsheet to try explain it better., if that helps but unsure how to load it on here . unless I can split the grants into months. and then just total it that way . as there are 6 departments, with various projects, all overlapping with various grants, sometimes running at the same time.swanjohnny83 I am attaching your mock data sheet with a LAMBDA I created to calculate those year fractions. See if this is what you need.