Why does TRIMMEAN return the same value as AVERAGE when I use 10% trim?

On a 10-value dataset, 10% trim removes 0.5 values from each end, which rounds down to 0, so no trimming occurs. You need more data points or a higher trim percentage to see TRIMMEAN differ from AVERAGE.

Can I use TRIMMEAN to exclude only rows that meet a condition (like a specific department)?

In Google Sheets, use an array formula: =TRIMMEAN(IF(D2:D11="IT", E2:E11), 0.2) with Ctrl+Shift+Enter. In Excel, use dynamic arrays or helper columns; TRIMMEAN does not natively support conditional row filtering like AVERAGEIF does.

How is TRIMMEAN different from MEDIAN?

MEDIAN returns the middle value(s) and is completely unaffected by outliers; TRIMMEAN removes a specified percentage from extremes and then averages the rest. TRIMMEAN gives you control over how much to dampen outlier impact.

What trim percentage should I use for my analysis?

Start with 10–20% for mild outlier dampening, or 30–50% for aggressive filtering. Test on your data and choose based on your use case: 20% is common in financial benchmarking, while 50% is useful for conservative forecasts.

TRIMMEAN function

Returns the mean (average) of a dataset after trimming a percentage of values from the top and bottom to remove outliers.

=TRIMMEAN(array, percent)

Generate a TRIMMEAN formula

Describe what you need. The generator will reach for TRIMMEAN where TRIMMEAN is the right tool, and tell you when it is not.

Arguments

How TRIMMEAN reads its arguments

Argument	Required	Description
array	Required	A range or array of numeric values to average. Must contain at least 2 values; text or non-numeric entries cause #VALUE! errors.
percent	Required	A decimal value between 0 (inclusive) and 1 (exclusive) representing the total fraction to trim from both ends combined (e.g., 0.2 trims 20% total, not per side).

Returns

A numeric value representing the trimmed mean (average) of the input array after removing extreme values.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Establish baseline salary average with minimal adjustment

Employee ID	Name	Department	Hire Date	Salary	Status
1	Alice Chen	Sales	2020-01-15	30000	Active
2	Bob Davis	IT	2019-03-22	120000	Active
3	Carol Singh	HR	2021-06-10	40000	Active
4	David Kim	Sales	2018-11-05	50000	Active
5	Eve Martinez	IT	2022-02-14	130000	Active
6	Frank Wilson	Finance	2020-09-28	60000	Active
7	Grace Lee	Sales	2019-12-01	45000	Active
8	Henry Brown	IT	2023-04-11	80000	Active
9	Iris Zhang	HR	2021-11-19	35000	Active
10	Jack Taylor	Finance	2017-05-30	55000	Active

=TRIMMEAN(E2:E11, 0.1)

Result: 64500

A 10% trim on 10 employees removes 0.5 values from each end, which rounds down to 0, so no values are actually excluded. The result ($64,500) is identical to AVERAGE and serves as a baseline to show the impact of stronger trims.

2. Calculate fair market value excluding extreme salaries

Employee ID	Name	Department	Hire Date	Salary	Status
1	Alice Chen	Sales	2020-01-15	30000	Active
2	Bob Davis	IT	2019-03-22	120000	Active
3	Carol Singh	HR	2021-06-10	40000	Active
4	David Kim	Sales	2018-11-05	50000	Active
5	Eve Martinez	IT	2022-02-14	130000	Active
6	Frank Wilson	Finance	2020-09-28	60000	Active
7	Grace Lee	Sales	2019-12-01	45000	Active
8	Henry Brown	IT	2023-04-11	80000	Active
9	Iris Zhang	HR	2021-11-19	35000	Active
10	Jack Taylor	Finance	2017-05-30	55000	Active

=TRIMMEAN(E2:E11, 0.2)

Result: 60625

With 20% trim, one employee is excluded from each extreme: Alice at the low end ($30,000) and Eve at the high end ($130,000). The trimmed mean of $60,625 represents typical compensation by removing the outlier earners (intern and executive) from consideration.

3. Analyze core workforce for conservative budget forecasting

Employee ID	Name	Department	Hire Date	Salary	Status
1	Alice Chen	Sales	2020-01-15	30000	Active
2	Bob Davis	IT	2019-03-22	120000	Active
3	Carol Singh	HR	2021-06-10	40000	Active
4	David Kim	Sales	2018-11-05	50000	Active
5	Eve Martinez	IT	2022-02-14	130000	Active
6	Frank Wilson	Finance	2020-09-28	60000	Active
7	Grace Lee	Sales	2019-12-01	45000	Active
8	Henry Brown	IT	2023-04-11	80000	Active
9	Iris Zhang	HR	2021-11-19	35000	Active
10	Jack Taylor	Finance	2017-05-30	55000	Active

=TRIMMEAN(E2:E11, 0.5)

Result: 55000

With 50% trim, two salaries are excluded from each end (30K, 35K at the bottom; 120K, 130K at the top), leaving the middle 6 employees. The result of $55,000 isolates the core workforce and is appropriate for conservative budget scenarios that ignore both entry-level and executive roles.

Common errors

Which TRIMMEAN error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The percent argument is outside the valid range of 0 to 1. A common mistake is entering 20 (meaning 20%) instead of 0.2, or using negative values like -0.1.	Convert the percentage to a decimal: 20% becomes 0.2, 50% becomes 0.5. Ensure the value is ≥0 and <1.
#VALUE!	The array argument contains non-numeric values such as text, dates stored as text, or mixed data types. TRIMMEAN cannot perform calculations on text.	Verify the array references only numeric columns (like salary, not department or hire date). Exclude or filter out text entries before passing to TRIMMEAN.
#REF!	The array range references cells that no longer exist, such as a deleted column or moved data. For example, =TRIMMEAN(E2:E11, 0.2) breaks if column E is deleted.	Restore the deleted column, update the formula to reference the correct column, or rebuild the reference range. Consider using named ranges for stability.

Tips and when to use something else

TRIMMEAN removes values symmetrically from both extremes. With 20% trim on 10 values, one value is removed from the top and one from the bottom (not two from one side).
When the trim percentage is small and rounds to zero (e.g., 10% on 10 values removes 0.5, rounded down to 0), TRIMMEAN behaves exactly like AVERAGE—use AVERAGE instead to simplify your formula.
Unlike manually filtering or using IF statements to remove outliers, TRIMMEAN calculates the robust mean without modifying your source data, making it cleaner for one-off analyses.
Use AVERAGEIF or AVERAGEIFS instead of TRIMMEAN when you need to filter rows by criteria (e.g., only IT department); TRIMMEAN is designed for symmetric trimming of extremes, not conditional row filtering.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.MEDIANMEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.MODE.SNGLReturns the most commonly appearing numeric value in a dataset, ideal for identifying the most frequent priority level, rating, or resolution time.MINMIN finds and returns the smallest numeric value in a range or list, automatically excluding text and empty cells.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.

Frequently asked questions

Why does TRIMMEAN return the same value as AVERAGE when I use 10% trim?: On a 10-value dataset, 10% trim removes 0.5 values from each end, which rounds down to 0, so no trimming occurs. You need more data points or a higher trim percentage to see TRIMMEAN differ from AVERAGE.
Can I use TRIMMEAN to exclude only rows that meet a condition (like a specific department)?: In Google Sheets, use an array formula: =TRIMMEAN(IF(D2:D11="IT", E2:E11), 0.2) with Ctrl+Shift+Enter. In Excel, use dynamic arrays or helper columns; TRIMMEAN does not natively support conditional row filtering like AVERAGEIF does.
How is TRIMMEAN different from MEDIAN?: MEDIAN returns the middle value(s) and is completely unaffected by outliers; TRIMMEAN removes a specified percentage from extremes and then averages the rest. TRIMMEAN gives you control over how much to dampen outlier impact.
What trim percentage should I use for my analysis?: Start with 10–20% for mild outlier dampening, or 30–50% for aggressive filtering. Test on your data and choose based on your use case: 20% is common in financial benchmarking, while 50% is useful for conservative forecasts.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17