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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average of Subtotals in Pivot Table 0
N/A in pivot table become 0 1
Pivot Table with Data Model Connection - getpivotdata reference issue 0
2007 Pivot table Calculated field misbehaving 1
Subtotal based on a range of data in Pivot Table 5
Don't show all Pivot table subtotals 1
calculated field - Pivot Table 1
Excel VBA For excel 0

Back
Top