CumulativeFunction

[Guest]

Week Analysis Client Rel Costin
AD 1 3 5
2 4 0.5 0.
3 7 13.5
4 3.5 0.
5 2.5
6 2.5
7 1.5 3.
8 8.5 5 6.
9
10

the above is from a pivot table - i am trying to create a formula which for example, takes the total hrs up to week 7 Client Rel (=29), and from this subtracts hrs up to week 2 (=5.5), so in essence im looking for a cumulative function which includes the GETPIVOTDATA function, so a short way of doing the following

ActiveCell.FormulaR1C1 =
"=GETPIVOTDATA(""Hours"",R3C1,""Week"",1,""Position"",""AD"",""Activity Desc"",""Analysis, RW& Pres."") + GETPIVOTDATA(""Hours"",R3C1,""Week"",2,""Position"",""AD"",""Activity Desc"",""Analysis, RW& Pres."")"
GETPIVOTDATA(""Hours"",R3C1,""Week"",3,""Position"",""AD"",""Activity Desc"",""Analysis, RW& Pres."")
..........................................................................

where the week no is captured from another cell.. hope this makes sens
I think i may need to use loops or somethin
Jame

 

[Tom Ogilvy]

=sumif(A:A,"<="&G9,C:C)-sumif(A:A,"<=2",C:C)

where G9 holds 7.

Although you might need to handle week AD specifically

--
Regards,
Tom Ogilvy

Week Analysis Client Rel Costing
AD 1 3 5
2 4 0.5 0.5
3 7 13.5 7
4 3.5 0.5
5 2.5 2
6 2.5 1
7 1.5 3.5
8 8.5 5 6.5
9
10 1

the above is from a pivot table - i am trying to create a formula which

for example, takes the total hrs up to week 7 Client Rel (=29), and from
this subtracts hrs up to week 2 (=5.5), so in essence im looking for a
cumulative function which includes the GETPIVOTDATA function, so a short way
of doing the following:

ActiveCell.FormulaR1C1 = _

"=GETPIVOTDATA(""Hours"",R3C1,""Week"",1,""Position"",""AD"",""Activity
Desc"",""Analysis, RW& Pres."") +
GETPIVOTDATA(""Hours"",R3C1,""Week"",2,""Position"",""AD"",""Activity
Desc"",""Analysis, RW& Pres."")" +

GETPIVOTDATA(""Hours"",R3C1,""Week"",3,""Position"",""AD"",""Activity

Desc"",""Analysis, RW& Pres."")"

 

[Guest]

Thanks Tom