If Statement

[Joy]

I have a series of data for projects with a project start date, a project end
date and a total amount for each project. I need to break the amount for
each project down into years by the no of days the project ran in each of the
years it ran (i.e. putting an amount under each year 2002, 2003, 2004). I am
assuming an if statement breaking the amount down to a cost per day would be
the way to go but am not sure how to go about getting data seperated for each
year.
Joy

 

[Arvi Laanemets]

Hi

Let's assume you data are on sheet Projects, and the table starts from
column A with headers in row1.
Project ; Start ; End ; Amount

Define a named range ProjectTable
ProjectTable=OFFSET(Projects!$A$1,1,,COUNTA(Projects!$A:$A)-1,4)

Add a sheet Summary, with a table on it:
Project ; 2002 ; 2003 ; 2004 etc

, and enter your project names/ID's into 1st column (or use links to sheet
Projects).

Select cell B2 on sheet Summary (NB! Be sure the cell is selected!) and
define named ranges
ProjStart=VLOOKUP(Summary!$A2,ProjectTbl,2,0)
ProjEnd=VLOOKUP(Summary!$A2,ProjectTbl,3,0)
ProjStartY=YEAR(ProjStart)
ProjEndY=YEAR(ProjEnd)
ProjAmount=VLOOKUP(Summary!$A2,ProjectTbl,4,0)

NB! Be sure relative/absolute references are exactly as in my example! Those
are dynamic named ranges, and active cell placement and reference types
affect directly how those ranges work later!

Into cell B2 on sheet Summary enter the formula
=IF(AND(ProjStartY<=B$1,ProjEndY>=B$1),(DATEDIF(MAX(DATE(B$1,1,1),ProjStart),MIN(DATE(B$1+1,1,0),ProjEnd),"D")+1)/(DATEDIF(ProjStart,ProjEnd,"D")+1)*ProjAmount,"")
, and copy the formula to whole summary table.
It's all.

Of-course you can add year columns directly to your original table. When you
do this, you have to alter definitons for names like Proj??? , and also
change year references in final formula.



Arvi Laanemets