Period of Performance calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BUD1888 - 26 Feb 2008 21:46 GMT

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

Reply to this Message

Fred Smith - 26 Feb 2008 22:16 GMT

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.

>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
[quoted text clipped - 10 lines]
>
> Bud1888

Reply to this Message

BUD1888 - 27 Feb 2008 13:06 GMT

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.

> What's in B6:B15? What do you want to appear in C20:etc? How many years in
> the maximum range?
[quoted text clipped - 18 lines]
> >
> > Bud1888

Reply to this Message

Fred Smith - 27 Feb 2008 13:20 GMT

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

> This is in Column A6:B15 -
>
[quoted text clipped - 42 lines]
>> >
>> > Bud1888

Reply to this Message

BUD1888 - 27 Feb 2008 19:06 GMT

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

> 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
[quoted text clipped - 49 lines]
> >> >
> >> > Bud1888

Reply to this Message

Fred Smith - 27 Feb 2008 19:56 GMT

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

> Sorry - I wish I could upload the file and then it might be easier to
> understand.
[quoted text clipped - 66 lines]
>> >> >
>> >> > Bud1888

Reply to this Message

BUD1888 - 28 Feb 2008 19:51 GMT

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

> So the formula you are looking for it the annual amount, calculated by
> =Amount / #Years. Is that correct?
[quoted text clipped - 80 lines]
> >> >> >
> >> >> > Bud1888

Reply to this Message