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.

LFMN	0.0826
LFMN	0.0834
LFMN	0.0829
LFMN	0.0826
LFMN	0.0834
LFMN	0.1305
LFMN	0.3058
LFMN	0.0452
LFMN	0.2625
LFMN	0.2640
LFMN	0.6166
LFMN	0.2887
LFMN	0.0853
LFMN	-
LFMN	0.0792
LFMN	0.0854
LFMN	0.0386
LFMN	0.4018
LFMN	0.0853

<tbody>
</tbody>

 

[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.