Period of Performance calculations

[BUD1888]

I have a spreadsheet with a wide assortment of data. I have a period of
performance with start and end dates. What I want to do is to take those
dates and have a formula that will spread the necessary data across a period
of time that matches the POP.

Example:
POP Start 01/01/2008(B2) End 12/31/2014(C2)
Data: B6:B15
Years 2007 (C20); 2008 (D20); 2009 (E20); etc.

Any help would be great.

Bud1888

 

[Fred Smith]

What's in B6:B15? What do you want to appear in C20:etc? How many years in
the maximum range?

An example would greatly help.

Regards,
Fred.

 

[BUD1888]

This is in Column A6:B15 -

Total Undiscounted Orders

501 1.0
502 1.0
ETC.

The POP is in D2/E2

This is in Column A60:B80 -

Department No/Name 2007 2008

501
502
etc.

Goes out to year 2017.

Thanks.

 

[Fred Smith]

Sorry, but I'm still in the dark. I have no idea what you're looking for.
You'll need to define Period of Performance, and give an example of the
results you want.

Regards,
Fred

 

[BUD1888]

Sorry - I wish I could upload the file and then it might be easier to
understand.

The POP can be any time period but for this example lets say
01/01/2008-12/31/2004

What I am trying to do is based on the POP, have a formula that spreads to
total order $ across the proper timeperiod. So - if the total order $ is
$1.5M then for years 2008 thru 2014 - the row would show $250K for each year.
Is there an formula that can be used to accomplish this.

Bud1888

 

[Fred Smith]

So the formula you are looking for it the annual amount, calculated by
=Amount / #Years. Is that correct?

If the start date is in D2, the end date in E2, and the amount is in B5,
use:

=B5/(year(e2)-year(d2))

This should equal the $250K you give in your example. Note that the formula
assumes your period contains complete years.

Does this help,
Fred

 

[BUD1888]

Yes - Thank you. It is one of those that you rack your brain and you know
there is an easy answer but it just doesn't come to you. Thanks again!!

Bud1888