Milestone Calculations

[eckert1961]

Hello,

I have an Excel 2007 workbook that contains data exported from Microsoft Project 2003. I need to calculate the Milestone Payments and I'm not sure what the best approach is.

The costs are in Column I or J depending on the structure of the plan. Milestones are in Column B.

If costs are in Column I, I want to sum the amounts until The Test for the value = MILESTONE is True and insert the Total in the same row as the Milestone in the Cost column.

For example, the 1st occurrance of Milestone is in Row 149 so the sum will be calculated from I16:I148 and the total will be entered into I149. Once that total has been entered I want to continue on through the remainder of the worksheet until I reach the final occurrance of MILESTONE.

Here is a sample of my data.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
128				Interface Design for One Link Application (remove scope)
129					Interface Requirement specification - Meeting Time
130							Client	2	$ -	23/05/2011
131					Interface Requirement specification - Meeting Time
132							Upside Software Solution Architect	2	$ 330.00	23/05/2011
133					Interface Technical specification - Meeting Time
134							Upside Software Senior Technical Analyst	2	$ 330.00	23/05/2011
135					Interface - Specification Document
136							Upside Software Solution Architect	8	$ 1,320.00	23/05/2011
137					Interface - Technical Specification Document Creation
138							Upside Software Senior Technical Analyst	18	$ 2,970.00	23/05/2011
139					Review Interface with client for Sign Off
140							Upside Software Solution Architect	1	$ 165.00	03/06/2011
141					Review Interface with client for Sign Off
142							Upside Software Senior Technical Analyst	1	$ 165.00	03/06/2011
143					Review Interface with client for Sign Off
144							Client	1	$ -	03/06/2011
145					Interface Specification Document - Clean up/Rework
146							Upside Software Solution Architect	8	$ 1,320.00	03/06/2011
147					Interface Specification Document - Clean up/Rework
148							Upside Software Senior Technical Analyst	8	$ 1,320.00	03/06/2011
149		MILESTONE: Completion of Business Process Review and Analysis
Sheet4

Excel 2007

Any assistance would be appreciated. Thanks.

Regards,
Chris

 

[eckert1961]

Hello,

Does anyone have any ideas on what is the best approach? Please let me know if I need to provide additional information or clarification. Thanks.

Regards,
Chris

 

[eckert1961]

I played around with the following formula.

=SUMIF(Client!B15:B149,"",Client!I15:I149)

It works but I know where the value Milestone is in the data that I'm working with. Since the milestones will vary from project to project I need an automated method to determine where the Milestones are in Column B.

What I want is for code or formula to test each cell in Column B until it locates the cell where the value Milestone is entered. In my data the cells in Column B will either contain nothing or the value Milestone. It would then calculate the costs in Column I from where the testing started until the row where Milestone was entered. Once the 1st Milestone calculation is made then the testing and calculations would continue until the end of data is reached.

Example:

The test would begin at B15 and Milestone is first found in B149. The total would be the sum of I15:I149. The test would then resume starting at B150.

I hope that I was able to explain it clearly enough. If not please let me know what would help.

Thanks,
Chris