Spreading a contract value over the duration of 4 years

[GH2017]

Hi all, I need to allocate a contract value, using a start date, and duration (number of months) from year 2023 to 2026.

I need a formula to populate the yellow highlighted range in the attached screenshot, and be able to drag it down, to provide the values I manually populated for an example.

Thank you

 

[dreid1011]

I've got to learn this LET function. Thanks for the knowledge..

The LET function only let me make it look cleaner and more readable. LET allows you to define a variable to use later in another function.

 

[GH2017]

Sorry, cleaning up a few more unnecessary bits of the formula:

=($J5/$I5)*LET(n,DATEDIF(EOMONTH($H5,-1),DATE(K$4,12,31),"m"),MAX(IF(n<=$I5,IF(n>=12,12,n),IF($I5-n+12>$I5,0,$I5-n+12)),0))

Really appreciate you going the extra mile. I copied the formula in and it's returning #NAME?. I checked the pointing, etc to make sure that everything's in line with your layout. Not a bother, have a great weekend and appreciate all of your help. Cheers.

 

[GH2017]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to allocate contract value over 4 years
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thank you! Have a great weekend and appreciate all the help.

 

[dreid1011]

Really appreciate you going the extra mile. I copied the formula in and it's returning #NAME?. I checked the pointing, etc to make sure that everything's in line with your layout. Not a bother, have a great weekend and appreciate all of your help. Cheers.

It could be LET if you are not really using 365. If that is the case, perhaps this:

=($J5/$I5)*MAX(IF(DATEDIF(EOMONTH($H5,-1),DATE(K$4,12,31),"m")<=$I5,IF(DATEDIF(EOMONTH($H5,-1),DATE(K$4,12,31),"m")>=12,12,DATEDIF(EOMONTH($H5,-1),DATE(K$4,12,31),"m")),IF($I5-DATEDIF(EOMONTH($H5,-1),DATE(K$4,12,31),"m")+12>$I5,0,$I5-DATEDIF(EOMONTH($H5,-1),DATE(K$4,12,31),"m")+12)),0)

Otherwise I am not sure what part would not be recognized.

 

[GH2017]

The LET function only let me make it look cleaner and more readable. LET allows you to define a variable to use later in another function.

That worked as well!! Thank you!!