Trimmean in Pivot Table

**botanybob** · 11-07-2014, 10:53 AM

*** See attached excel file ***
I have a pivot table with four 'Row' categories and one 'Value' category.
Row categories are Variety - Region - Location - Field Type
Value category is Harvest Yield

I would like to apply the TRIMMEAN formula to remove the highest 25% and lowest 25% and report the remaining average for each Harvest Yield value in the pivot table.

I could manually double click on each Harvest Yield value, opening up a new worksheet containing the data that made value, apply TRIMMEAN formula, copy and paste into a new column on pivot table. However, I would like a more dynamic solution, so I could remove or reorder 'Row Categories' (Variety, Region, etc) and still have TRIMMEAN function reported in results.

Thank you so much for your help folks

**botanybob** · 11-11-2014, 11:03 AM

I asked a friend and he told me to use the following which worked like a charm. Thanks Joe Joe.

Name a cell "TrimAmnt" and enter a value into the cell i.e. 0.25
*the value of cell can be changed to alter amount to be cut off each tail of data*

Turn RAW DATA into a Table

Make new column (Tmean) in RAW DATA and apply the following array to first cell then press (Ctrl + Shift + Enter) to apply the formula into every cell in column:
=TRIMMEAN(IF([REGONAL AREA]=[@[REGONAL AREA]],IF([LOCATION NUMBER]=[@[LOCATION NUMBER]],IF([VARIETY]=[@VARIETY],IF([FIELDTYPE]=[@FIELDTYPE],[HARVYIELD])))),TrimAmnt)

Make a new pivot table and set the Tmean column to MAX or MIN.

Trimmean in Pivot Table

LinkBack

Thread Tools

Rate This Thread

Display

Trimmean in Pivot Table

Re: Trimmean in Pivot Table

Thread Information

Users Browsing this Thread

Similar Threads

TrimMean of Pivot Table data sets

[SOLVED] Using TRIMMEAN on a selection of data

[SOLVED] TRIMMEAN & OFFSET together

Can i use trimmean in pivot tables

[SOLVED] TRIMMEAN with different percentiles?

Bookmarks

Bookmarks

Posting Permissions