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
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