Trimmean If without array

[Ruca13]

Hi everyone.

Does anyone have any idea how I can bypass the problem of using an array formula when I use the trimmean with criteria?

The excel is already extremely slow and the array just blocks it completely

Thanks,

Rui

 

[Dave Patton]

Please post a concise example of your challenge and the expected result.

 

[Ruca13]

Thank you for your reply

Dropbox - Capturar.PNG

I have trimmean to exclude 15% of the outliers.

The formula reads like this:

{=TRIMMEAN(IF((L:L=L9)*(AE:AE<>0),AE:AE),0.15)}

It has the expected result but I need and alternative.

Unfortunately I cannot have to run with arrays (I have not calculated the worksheet, just those cells where I applied trimmean), because the file is already loaded with formulas and has more than 5k rows.

Thank you

 

[Dave Patton]

You did not post an example.

Try your formula with only the relevant rows for example L2:L500.

You could look at Aggregate or other functions but excluding the 15% would complicate the formula.

 

[Ruca13]

I have data like i show below, where as you can see, some values are way too high, and others too low. I want to exclude those.

For that my idea was to do a mean were I can exclude outliers, and if a value x times higher or x times lower than that mean I calculated, it would be excluded.

I need it to depend on the 1st column, and I also excluded zero.

The 15% are just an example, I will define a value but for now it isn't decisive for me.

I do not think I can make it clearer than this.

[TABLE="width: 154"]
<tbody>[TR]
[TD]LFMN[/TD]
[TD] 0.0826[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0834[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0829[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0826[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0834[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.1305[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.3058[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0452[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.2625[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.2640[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.6166[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.2887[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0853[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0792[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0854[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0386[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.4018[/TD]
[/TR]
[TR]
[TD] LFMN[/TD]
[TD] 0.0853[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

2 thoughts that may help.

Excel 2010
	A	B	C	D	E
8					Helper
9	LFMN	0.0826	0.153038		1
10	LFMN	0.0834	0.153038		1
11	LFMN	0.0829			1
12	LFMN	0.0826			1
13	LFMN	0.0834			1
14	LFMN	0.1305			1
15	LFMN	0.3058			1
16	LFMN	0.0452			1
17	LFMN	0.2625			1
18	LFMN	0.264			1
19	LFMN	0.6166			1
20	LFMN	0.2887			1
21	LFMN	0.0853			1
22	LFMN	0			0
23	LFMN	0.0792			1
24	LFMN	0.0854			1
25	LFMN	0.0386			1
26	LFMN	0.4018			1
27	LFMN	0.0853			1
28
8a

Cell Formulas
Range	Formula
E9	=(A9=A9)*(B9<>0)
C9	{=TRIMMEAN(IF((A9:A50=A9)*(B9:B50<>0),B9:B50),0.15)}
C10	{=TRIMMEAN(IF((E9:E50=1),B9:B50),0.15)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ruca13]

While I appreciate the time and effort you took, I think I will not use your solution, since I stated I want to avoid array formulas.

Regarding your answer, that would only work if column 1 was always the same. In the link I show that isn't the case. That won't be reflected in the C10 formula as it is in C9.

 

[Dave Patton]

The suggestions use a range of rows that include the data; full column references
can be resource intensive.

The formula in C9 would be copied or filled down and then converted to value.
The array formula C10 uses less resources.

 

[Ruca13]

Unfortunately the best I could do was indeed paste as values after calculate (I calculate only that part of the sheet using a very small macro).

I have adapted the ranges and added some include/exclude controls to have other formulas run quicker.
I guess there really isn't an alternative to using an array in Trimmean.

Thank you for your replies.

 

[Dave Patton]

My message was not clear.
The Formula in C10 uses less resources.
The Helper column assembles some of the criteria E9 =(A9=A9)*(B9<>0)
The helper column can be easily converted to a value 1 or 0.

With the suggestions, hopefully the array formula will work.

A non array formula could be built but it would be complicated and it might use lots of resources as well.