Using MODE in Pivot Tables

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

Any ideas how to calculate MODE in a pivot table? I keep
getting #N/A as a result. Also can you get the SUBTOTAL
function to calc MODE?
 
Create an additional column with as label, say, Frequency and determine the
frequency of occurrence of the relevant items. Include this column in the
pivot table and apply Max to the Frequency field. Activate Field Settings
after building the pivot table, then click on the Advanced button. Set
AutoShow options to Automatic, choose 1 for Show and choose Max of Frequency
for Using field.

Let A:C house the source data. Lets suppose that the relevant items are in
column A with X as label in A1.

In D1 enter the label: Frequency

In D2 enter & copy down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF(A:A,A2))
 
Back
Top