Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable question
Dylan wrote:
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 A1:D7 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable Question | Excel Worksheet Functions | |||
PivotTable Question | Excel Discussion (Misc queries) | |||
PivotTable Question | Excel Worksheet Functions | |||
PivotTable Question | Excel Discussion (Misc queries) | |||
PivotTable Question | Excel Discussion (Misc queries) |