Need help with Excel formula

Hello-

I have the following worksheet attached with the following fields.

Tracking ID
Project date
Project value
Savings
Year
Project Length

I am trying to come up with a formula to calculate project value by how long the project is. For example if the project date is 1/1/2014, the project was for 2 years, and valued at $100, the project value by year would be $50 in 2014 and $50 in 2015.

See attached file.
tawathavAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
barry houdiniConnect With a Mentor Commented:
Hello Brad,

I tried that longer formula with the "Project Date" for Project id 2 set to 1st May 2014 for all rows - the values returned for all years were as I expected except for 2019 when I got 1328.77 instead of the expected 328.77.

I think you can use this formula in row 2 copied down

=C2/F2*MAX(0,MIN(DATE(E2+1,1,1),EDATE(B2,F2*12))-MAX(B2,DATE(E2,1,1)))/(365+COUNT(E2&"-2-29"))

see comparison attached

regards, barry
Savings-worksheet-barry.xlsx
0
 
NBVCCommented:
no attachment....
0
 
tawathavAuthor Commented:
0
 
byundtCommented:
If you don't need to pro-rate projects for partial years, you could use:
=AND(E2>=YEAR(B2),E2<=YEAR(B2)+F2-1)*C2/F2
0
 
byundtCommented:
If years need to be pro-rated because projects don't always begin on January 1, then the formula gets more complex:
=IF(YEAR(B2)=E2,(DATE(YEAR(B2)+1,1,1)-B2)/(365+(MOD(YEAR(B2),4)=0)),
IF(YEAR(DATE(YEAR(B2)+F2,MONTH(B2),DAY(B2))-1)=E2,(DATE(YEAR(B2)+F2,MONTH(B2),DAY(B2))-1-DATE(YEAR(B2)+F2-1,1,0))/(365+(MOD(E2,4)=0)),
AND(E2>=YEAR(B2),E2<=YEAR(B2)+F2-1)))*C2/F2
0
All Courses

From novice to tech pro — start learning today.