Excel07 - pvt tbl - calculated field question

[tagoso]

Hi,
I have a few thousand lines of data.
Each line contains;

Date: ProjectName : HoursWorked

I have successfully created a pivot table showing number of hours for each project and I can even filter by month.

What I want to do now is create a calculated field which tells me how much money those HoursWorked cost us. Each month the accountant gives me the total $ figure for labour expense. I divide this by the total number of hours worked in that month to give me an average rate per hour. I would then like to apply that rate to each project and get a cost of labour for each project.

I have tried creating a calculated field but Excel wont let me reference a cell external to the pivot table (namely the average hourly rate).
Any brilliant ideas?
Thanks in advance.
bryan

 

[PaddyD]

Hi - welcome to the board!

you can't use a different constant for, eg, each month break in a calculated field. You'll need to have a single value for the lot, or add what you need to the data & let the pivot do the work.