I have the following data, but much longer. What I want to do is take the average of the total summed hours. I have categories with sub categories with sub categories below that. In the pivot example below when I select average it averages by person. What I want is the average by the total amount of hours by Bar while having the names on the pivot. So instead of 27.946 average by name I would like the average of the bars 125.76 (140.33+111.19)/2.
Maybe I have to add another column to the raw data, but if I do a sumif, averageif, or countif etc. it will have duplicates.
[TABLE="width: 548"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 238"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Manufacturing[/TD]
[/TR]
[TR]
[TD]Soap[/TD]
[TD="align: right"]251.52[/TD]
[/TR]
[TR]
[TD]Bar 1[/TD]
[TD="align: right"]140.33[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD="align: right"]21.91[/TD]
[/TR]
[TR]
[TD]Sebby[/TD]
[TD="align: right"]118.42[/TD]
[/TR]
[TR]
[TD]Ralph[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bar 2[/TD]
[TD="align: right"]111.19[/TD]
[/TR]
[TR]
[TD]Ralph[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sebby[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD="align: right"]67.76[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD="align: right"]43.43[/TD]
[/TR]
</tbody>[/TABLE]
Maybe I have to add another column to the raw data, but if I do a sumif, averageif, or countif etc. it will have duplicates.
[TABLE="width: 548"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 238"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Manufacturing[/TD]
[/TR]
[TR]
[TD]Soap[/TD]
[TD="align: right"]251.52[/TD]
[/TR]
[TR]
[TD]Bar 1[/TD]
[TD="align: right"]140.33[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD="align: right"]21.91[/TD]
[/TR]
[TR]
[TD]Sebby[/TD]
[TD="align: right"]118.42[/TD]
[/TR]
[TR]
[TD]Ralph[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bar 2[/TD]
[TD="align: right"]111.19[/TD]
[/TR]
[TR]
[TD]Ralph[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sebby[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD="align: right"]67.76[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD="align: right"]43.43[/TD]
[/TR]
</tbody>[/TABLE]
Last edited: