Need help with Excel formula

Posted on 2014-01-14
Last Modified: 2014-02-05

I have the following worksheet attached with the following fields.

Tracking ID
Project date
Project value
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.
Question by:tawathav
LVL 23

Expert Comment

ID: 39780319
no attachment....

Author Comment

ID: 39780335
LVL 81

Expert Comment

ID: 39780362
If you don't need to pro-rate projects for partial years, you could use:
LVL 81

Expert Comment

ID: 39780549
If years need to be pro-rated because projects don't always begin on January 1, then the formula gets more complex:
LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 39780659
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


see comparison attached

regards, barry

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select only certain columns not entire sheet 12 26
.Range Set 25 74
Excel Calculate Average - Grouped Values 7 22
And OR formula 5 20
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now