Average Data in Pivot with Sum in Total

[Tammy Levasseur]

I have a spreadsheet with data and I want to show the average of the data by discipline (which is doable) but in the Total I want to sum the average data. Is this possible. When I add the Total to the Pivot it provides the average of the data. Below is a sample: CHK, DES and ENG are disciplines and the data is an average of each discipline on the 3533001.2521 row I would like a total of the averaged data. Let me know if there are any solution. I've read about power pivot. Not sure if that is what is required.

3533001.2521	22	33	67	41
CHK	1	4	3	3
DES	20	20	53	31
ENG	1	9	11	7

<tbody>
</tbody>

 

[oldbrewer]

can you just calculate it from the original data ? ie don't use a pivot table ?

 

[Tammy Levasseur]

There are several layers of grouping. The above is just a small snippet of the actual pivot. I would have to do a lot of averageif statements and then sum that. It might be a solution if I can't get the pivot to work.

 

[oldbrewer]

								test1	test2	test3	test4
CHK	test1	1					CHK	1	4	3	3
DES	test1	20					DES	20	20	53	31
ENG	test1	1					ENG	1	9	11	7
CHK	test2	4
DES	test2	20
ENG	test2	9
CHK	test3	3
DES	test3	53
ENG	test3	11
CHK	test4	3
DES	test4	31						the above table made with sumproduct formula
ENG	test4	7
								easy to add totals for rows and columns

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Tammy Levasseur]

Thank you,

The data should be an average. My above example is an average of a much larger data set. For example, in your example Test1 DES is 20. My data would say Test1 DES is 10,20, 30, My pivot table averages all that data to 20.

Hope I'm making sense.

Tammy

 

[oldbrewer]

where does 10,20,30 come from

is the layout of my data wrong - are all the tests test1 ?

 

[Tammy Levasseur]

Hi Oldbrewer

My example was an actual pivot table with the data averaged within. Not the raw data. The raw data would look something like this. Just a sample and does not align with my earlier pivot table however you can see Test1 DES is 10, 20,30 and the Pivot Table had averaged that data to 20.

		Actual Hours
Test1	DES	10
Test1	DES	20
Test1	DES	30
Test1	ENG	50
Test1	ENG	30
Test1	ENG	20
Test1	CHK	40
Test1	CHK	60
Test1	CHK	50
Test2	DES	20
Test2	DES	30
Test2	DES	50
Test2	ENG	60
Test2	ENG	40
Test2	ENG	50
Test2	CHK	10
Test2	CHK	20
Test2	CHK	20

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

 

[oldbrewer]

Test1

DES

10

Test1

DES

20

Test1

DES

30

Test1

ENG

50

Test1

ENG

30

Test1

ENG

20

Test1

CHK

40

Test1

CHK

60

DES

ENG

CHK

Test1

CHK

50

Test1

20

Test2

DES

20

Test2

Test2

DES

30

Test2

DES

50

Test2

ENG

60

Test2

ENG

40

20 COMES FROM

Test2

ENG

50

Test2

CHK

10

=SUMPRODUCT(($A$1:$A$18=$G9)*($B$1:$B$18=H$8)*($C$1:$C$18))/3

Test2

CHK

20

Test2

CHK

20

I have divided by 3 but not hard to calculate the 3 to allow for more raw data

you have 18 rows, 2 tests = 9

you have 3 DES ENG CHK 9/3 = 3

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Tammy Levasseur]

Thanks, I'll give it a try. I was hoping to keep in in a pivot table because there is a filter variable that I wanted to be able to switch between but it may not be possible.

Thanks

Tammy