Calculate total cost/time per completed project.

[Vaguely]

Hey all,

I have a set of data I'm trying to calculate the cost / time per completed project. A completed project is one that has gone to "Prod". (production).

I want to figure out a formula that takes the below data and calculates for me the COST (or time) spent on projects that are complete (have "Prod") and tell me what period it was completed in.

It would be amazing to figure out a user friendly / maintainable / scale able way of doing this

Example Final:
Period: 11 -> manually entered
Cost: 410 -> no idea how this work. thoughts (Index + Aggregate) or using pivot table?
Time: 148 -> (same as above)
Projects: 1 -> (Countifs should work here)

Project A:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Project Stage[/TD]
[TD]Period[/TD]
[TD]Cost[/TD]
[TD]Time Spent[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Dev[/TD]
[TD]8[/TD]
[TD]105[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Sit[/TD]
[TD]9[/TD]
[TD]108[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Bat[/TD]
[TD]10[/TD]
[TD]94[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Prod[/TD]
[TD]11[/TD]
[TD]103[/TD]
[TD]19[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

you mean like this

Excel 2007
	A	B	C	D	E	F
1	Project Name	Project Stage	Period	Cost	Time Spent	total time / cost
2	A	Dev	8	105	21	148 days
3	A	Sit	9	108	53	410 dollars
4	A	Bat	10	94	55
5	A	Prod	11	103	19
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIF(A2:E5,"A",E2:E5) & " days"
F3	=SUMIF(A2:E5,"A",D2:D5) & " dollars"

 

[Vaguely]

I mean like this; It has to be tied back to be period specific.

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Cost[/TD]
[TD]Time Spent[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]410[/TD]
[TD]148[/TD]
[/TR]
</tbody>[/TABLE]

 

[Vaguely]

I'll update the data set so that it's more clear what i'm working with.


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Project Stage[/TD]
[TD]Period[/TD]
[TD]Cost[/TD]
[TD]Time Spent[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Dev[/TD]
[TD]8[/TD]
[TD]105[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Sit[/TD]
[TD]9[/TD]
[TD]108[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Bat[/TD]
[TD]10[/TD]
[TD]94[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Prod[/TD]
[TD]11[/TD]
[TD]103[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Dev[/TD]
[TD]1[/TD]
[TD]66[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Sit[/TD]
[TD]5[/TD]
[TD]70[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Bat[/TD]
[TD]10[/TD]
[TD]53[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Dev[/TD]
[TD]1[/TD]
[TD]90[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Sit[/TD]
[TD]6[/TD]
[TD]94[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Prod[/TD]
[TD]9[/TD]
[TD]48[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

 

[Vaguely]

sorry for multiple posts, i can't seem to edit mine.

Essentially, I only want to sum the projects that have gone through the "PROD" stage. So in the updated sampel set, we would sum A & C but not B.

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Cost[/TD]
[TD]Releases[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]232[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]410[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Maybe something like this VBA
Assuming data is as follows:

Excel 2007
	A	B	C	D	E	F	G	H
1	Project Name	Project Stage	Period	Cost	Time Spent	Project Name	Cost	Duration
2	A	Dev	8	105	21	C	232	134
3	A	Sit	9	108	53
4	A	Bat	10	94	55	A	410	148
5	A	Prod	11	103	19
6	B	Dev	1	66	48
7	B	Sit	5	70	27
8	B	Bat	10	53	70
9	C	Dev	1	90	37
10	C	Sit	6	94	27
11	C	Prod	9	48	70
Sheet2

Sub MM1()
Dim lr As Long, r As Long, n As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 0
n = 2
Range("F1").Value = "Project Name"
Range("G1").Value = "Cost"
Range("H1").Value = "Duration"

For r = lr To 2 Step -1
    If Range("B" & r).Value = "Prod" Then x = 1
    If x = 1 Then
     Range("F" & n).Value = Range("A" & r).Value
     Range("G" & n).Value = Range("G" & n).Value + Range("D" & r).Value
     Range("H" & n).Value = Range("H" & n).Value + Range("E" & r).Value
    End If
    If Range("A" & r).Value <> Range("A" & r - 1).Value Then
    x = 0
    n = n + 1
    End If
Next r
End Sub

 

[Michael M]

Improved code assumes data is on Sheet 2 and in columns as mentioned in my sample data

SSub MM2()
Dim lr As Long, r As Long, n As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 0
n = 2
Range("F1").Value = "Project Name"
Range("G1").Value = "Cost"
Range("H1").Value = "Duration"
For r = lr To 2 Step -1
    If Range("B" & r).Value = "Prod" Then x = 1
    If x = 1 Then
        Range("F" & n).Value = Range("A" & r).Value
        Range("G" & n).Value = Range("G" & n).Value + Range("D" & r).Value
        Range("H" & n).Value = Range("H" & n).Value + Range("E" & r).Value
    End If
    If Range("A" & r).Value <> Range("A" & r - 1).Value Then
        x = 0
        n = Cells(Rows.Count, "F").End(xlUp).Row + 1
    End If
Next r
With Worksheets("Sheet2")
    .Sort.SortFields.Add Key:=Range("F1"), SortOn:=xlSortOnValues, Order:=xlAscending
        With Worksheets("Sheet2").Sort
            .SetRange Range("F1:H" & lr)
            .Header = xlYes
            .Orientation = xlTopToBottom
            .Apply
        End With
End With
End Sub

 

[Vaguely]

This is helpful thank you so much! my own problem is that I actually have to pass this off after i'm done with it to slightly less technical people and I'm hoping that a simpler more bootleg solution would work here?

I still really appreciate your help though, the thinking in the VBA is definitely useful for logically interpreting the order.

I usually have some grasp of being able to figure it out, but this one is totally beyond me! No idea of what index, match, aggregate, or even supporting tables can do. My ultimate audience will be more a laymen... perhaps the vba is the right way to go in the end, but could we brainstorm a bit with the powerful excel functions / pivot tables? I think pivot tables are ruled out, but it does provide a nice aggregate per project. There just needs to be some sort of filter for it to display 1. next to a period in an aggregate form for that project & 2. sum only when 'prod' is seen. yea okay maybe pivot tables cant do that...

 

[Michael M]

There are forum members that can probably set it up with formulas, far better than I....but the VBA will allow for a dynamic number of projects and ranges.
If you don't get further replies in a day or so.....give it a BUMP by replying to your own post !!

 

[Vaguely]

The ideal is for me to be able to generate a graph with the aggregated data to plot a trend line of the costs. (let's ignore time for now as it can be replicated with costs after).

So for each Period (1 - 12, representing months) it will have on average the cost / completed project.

fundamentally this is how i think about it:
let table x = table that we will put this formula and be structured as the OUTPUT table. (same as the 3 column table as per previous post)
Let table y = table for input data 5 column table as per previous posts.
assume each project can have multiple stages not listed and repeating stages, but only 1 prod stage.

0. look at the table x, col1row1 to identify period. index()
1. Look (match) into column B - Stage and identify "Prod"
2. Return Project Value - A,B,C,D,E if found matching above
3. aggregate all values returned by value above sumif
4. Loop for all other "prods" in period??????????????????????? how............... stuck here;