We help IT Professionals succeed at work.

allocation of a value over a time period

Lourr
Lourr asked
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.

Sample of formats and desired resultThanks
Sample.pdf
Comment
Watch Question

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,"")

Open in new window

Regards
EE20150303.xlsx

Author

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

Author

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

Author

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