TRIMMEAN for 6 cells

[Michael67]

I would like to apply TRIMMEAN on 6 cells, discounting 2 of them and using the middle 4. I am getting 'too many arguments' returned. Could someone please advise how I can fix this.

 

[BlueHornet]

Welcome to the board.

The arguments for the function are ( array, percent), so you don't have a lot of leeway built into the function to adjust that array. If you can figure a way to make a single argument for "array" that already discounts the two values you don't want, then that would do the trick, but that's going to be messy and likely hard to explain (or recall later) when you try to audit or duplicate the process. So my suggestion is to arrange a 'helper' array of values (because you'll still want to have an array consisting of four values), that has already removed the two cells you want to ignore, and operate on that array. Having that 'helper' array constructed on the worksheet somewhere, even if it's later hidden for cosmetic reasons, will make the thing easier to explain or do again in the future sometime.

 

[Sufiyan97]

Hi Michael67
Can you post some rows of your data as an example

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you use something like this? (formula to be confirmed with Ctrl+Shift+Enter, not just Enter before dragging across)

22 04 14.xlsm
	A	B	C	D	E
1		12	56	82	22
2		99	95	83	22
3		2	35	56	22
4		54	7	79	22
5		75	95	61	22
6		15	57	37	22
7
8	Mean of middle 4:	39	60.75	69.5	22
Mean

Cell Formulas
Range	Formula
B8:E8	B8	=AVERAGE(LARGE(B1:B6,{2,3,4,5}))
Press CTRL+SHIFT+ENTER to enter array formulas.