Forum Discussion
Calculate hours using pivot table
Hi,
Im making a personell planning sheet and I want to calculute the hours each teacher is teaching using a pivot table. My data is formatted in 2 tables like this (simplified):
| Lesson | Hours | Teacher 1 | Teacher 2 |
| Lesson 1 | 2 | Paul | |
| Lesson 2 | 3 | Pete | |
| Lesson 3 | 2 | Paul | Pete |
| Teacher | Max working hours |
| Paul | 10 |
| Pete | 15 |
Now its easy to create an overview of how many hours each teacher is teaching with one teacher collumn.
But it need to calculate the hours based on 2 collumns like this:
- Paul -> Lesson 1 + Lesson 3 = 4 hours
- Pete -> Lesson 2 + Lesson 3 = 5 hours
Then the next step is to use a metric or KPI to calculate if the teacher is exceeding their max working hours.
I hope someone can help me out with this...
Thanks!
1 Reply
An alternative could be Power Query that works in Excel 2013 and more recent versions of Excel. In the attached file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet and the blue tables horizontally.
