Counting average number of unique outputs

[sultanijaz]

Hi all,

Trying to figure out how to do this, even a long winded way if anyone has any ideas.
[TABLE="width: 194"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Company[/TD]
[TD]Spend Category[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Gas[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Electricity[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]Water[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Electricity[/TD]
[/TR]
</tbody>[/TABLE]

I have something like the table above and the end goal is to calculate the average number of spend categories per company. Some companies will have more than one of the same spend categories.

Any help would be appreciated.

Thanks
Sultan

 

[SpillerBD]

AVERAGE of COUNTS is not a built in feature of Pivot Tables. You will have to go to PowerPivot/DAX formulas to feed a Pivot Table via data model.
You could also go old-school with an array-formula...