CODEWITHSUNDEEP
excelexcel-formula
user2043446
user2043446

Reputation: 23

How do I specify an array argument for trimmean function, when I am selecting from cells?

I am using TRIMMEAN formula in Excel. My input data cannot be selected into an array. This is what I would like to do: compute mean (with outlier removal) of six values in cells i2, i98, i194, i290,...

I have tried this 

=trimmean({I2;I98;I194;I290;I386;I482}, 0.2)

How do I create an array of the six values? Is there a way to use trimmean in this case?

Upvotes: 1

Views: 830

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60259

TRIMMEAN seems to accept a non-contiguous range if you enclose the non-contiguous range within parentheses ()

enter image description here

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152525

to do that you will need INDEX and Some Math:

=TRIMMEAN(INDEX(I:I,N(IF({1},(ROW(1:6)-1)*96+2))),0.2)

This is an array formula that needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

The INDEX passes an array of those values starting with row 2 and then for the next 5 rows adding 96.

Upvotes: 3

Related Questions