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'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.
"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.
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.
- HI, Thanks for helping with this. I appreciate been able to resolve this excel problem.
apologies for the delay getting back to you, I spend all of Friday trying to get the formula to work. I managed to sort by changing the date format. As I'm from the uk, date format is different; along with changes to the columns above. I will need to try learn to do this myself - yes that should work. Make sure all those dates are actually dates (not text that just looks like a date) and that the grant award amounts are actual values and lastly and maybe the most likely problem is that the headers 2014-2030 are just year values and not text and not dates formatted to only show the year.
- hi,
I tried the above formula, on the spreadsheet , I couldn't get it to work on the actual spreadsheet.
on the real spreadsheet, column E is the start date, column F is the end date and column T is the grant award amounts. so I am assuming this formula would work and K$1 would be changed to AC$1 - as headings 2014-2030 start between AC To AP12; as the columns start from row 2 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.
- Hi,
So I've loaded the spreadsheet and I see on the spreadsheet Err509.
I've managed to use formula to work out number of months between start date and end date . example =datedif(c2,d22, "m") this works out the number of months between 2018-01-01 and 2018-07-31 and 2018-08-01 to 2019-03-31 unless you have an easier formula that works. 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.
- If you can't attach here then you can use sharepoint or onedrive or similar and then share a link. if all else fails then you can PM it to me and I can attach it. That said, my point in the previous comment was that I don't understand how you are dividing the amounts. I'm thinking it may have been a mistake but so often I find it isn't a mistake but my misunderstanding and that is why I was clarifying. So in the example you found it to have 2.33 years and you broke it down into 1 year + 1 year + 0.33 years but the timeline was that the first year was only 3/4 year then 1 year then last year had 7/12 of a year. So if you evenly distributed I would expect the amounts to be 0.75/2.33, 1.0/2.33, 7/12 / 2.33 for the values for each of the years.