PivotTable question

[Dylan]

I have a PivotTable with a list of projects and the monthly cost for each
project. At the bottom of the table is several rows of non-project costs.

For example:
Project ID Jan Feb March etc. SubTotals
Project1 £50 £60
Project2 £60 £70
Project3 £70 £80
Cost 01 £20 £30 £40
Cost 02 £30 £40 £20
Cost 03 £40 £20 £20

With projects starting and ending in different periods, I want spread the
sum of the non-project cost across the projects that are active for each
month.

So with my example, the total non-project cost for Jan is £90 and active
projects are Project 1 and 3. Project 1 Value would increase to £95 and
Project 3 value to £115.

I have a little experience in using PivotTables and imagine it could be
done, can you please advise, or help me to set it up?

Kind regards
Dylan Dawson

 

[smartin]

I have a PivotTable with a list of projects and the monthly cost for each
project. At the bottom of the table is several rows of non-project costs.

For example:
Project ID Jan Feb March etc. SubTotals
Project1 £50 £60
Project2 £60 £70
Project3 £70 £80
Cost 01 £20 £30 £40
Cost 02 £30 £40 £20
Cost 03 £40 £20 £20

With projects starting and ending in different periods, I want spread the
sum of the non-project cost across the projects that are active for each
month.

So with my example, the total non-project cost for Jan is £90 and active
projects are Project 1 and 3. Project 1 Value would increase to £95 and
Project 3 value to £115.

I have a little experience in using PivotTables and imagine it could be
done, can you please advise, or help me to set it up?

Kind regards

Hello Dylan,

I am not sure a pivot table is the best place to do this. The following
works in a regular worksheet.

I placed your sample data at A17 and copied the row and column headers
(just the Project rows) to F1:I4

Then I placed this formula in G2, which can be filled right and down:
=IF(B2,B2+SUM(B$5:B$7)/COUNT(B$2:B$4),"")

Result:
Project ID Jan Feb March
Project1 95 90
Project2 90 150
Project3 115 110


Alternatively, this formula returns a weighted cost distribution, in
case you want to charge projects proportionately according to the
project cost:
=IF(B2,B2+(B2/SUM(B$2:B$4))*SUM(B$5:B$7),"")

Result:
Project ID Jan Feb March
Project1 87.5 87
Project2 87 150
Project3 122.5 116