We help IT Professionals succeed at work.

# allocation of a value over a time period

on
The attached file is what I am trying to achieve.
Column C is a percentage WP (win probability)
Column D is the total estimated Rev
Column E is the award or start date
Column F is the end date (calculated by taking the POP (period of Performance) column G  =+E2+(G2*30)
Column G is the POP
Columns K through O is what I'm trying to calculate.  In the example April should be the start date the value is the estimated Rev (col D) * the WP (col C) spread evenly in accordance to the date ranges Col E and Col F.

Thanks
Sample.pdf
Comment
Watch Question

## View Solution Only

Top Expert 2016

Commented:
Hi,

pls try

``````=IF(AND(MONTH(H\$1)<=MONTH(\$F\$2),MONTH(H\$1)>=MONTH(\$E\$2)),\$D\$2*\$C\$2/\$G\$2,"")
``````
Regards
EE20150303.xlsx

Commented:
Hi Rgonzo
Thanks for the formula.  A couple of additional questions.
1. Take a look at Sample2 attached.  I can't get the formula to work.  I'm pulling the data as a download excel table from SharePoint.  I assume I have a data type problem but I don't know where.
2. I took your file and added to it.  Anticipating my next requirement how do I adapt the formulas to address period of performances that go into 2016.  Take a look at the yellow highlighted areas in EE2015030_1.

I thank you my friend
Sample2.xlsx
EE20150303-1.xlsx
Top Expert 2016

Commented:
Then you we ma simplify

=IF(AND(H\$1<=\$F2,H\$1>=\$E2),\$D2*\$C2/\$G2,"")
EE20150303-2.xlsx

Commented:
Thanks again Rgonzo.  Still don't know why the formula doesn't work in the attached file.  The original formula returned #VALUE the simplified version returns nothing.  Any idea why?
sampe3.jpg
Sample3.xlsx
Top Expert 2016
Commented:
You have to replace the months with the first day of the month of of the corresponding year

instead of Jan  then 1/1/2015 and so on

then format these dates
Home /  Number / format Cells / Number / Custom

MMM

see my example file

Commented:
I thank you my friend, I knew it was something obvious I just couldn't see it.  You up for 1 more?