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.
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.
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 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, 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