Delete outliers and then calculate formula

[ssuresh98]

Hello All,

I need some help with this problem, please. It looks simple but I am clueless how to approach it.

Say I have an array

100

25

29

30

18

200

I would like to get the average for this array, but after deleting the outliers 100 and 200. Is there a way I can write-up a formula which can calculate the average for those rows which have <10% SD?


Thanks in advance.

 

[SirJB7]

Hi, ssuresh98!


Give a try to this formula:

=SUMAPRODUCTO(A1:A6;SI(A1:A6<DESVEST(A1:A6);1;0))/SUMA(SI(A1:A6<DESVEST(A1:A6);1;0)) -----> in english: =SUMPRODUCT(A1:A6,IF(A1:A6<STDEV(A1:A6),1,0))/SUM(IF(A1:A6<STDEV(A1:A6),1,0))


It's an array formula so you should enter it with Ctrl-Shift-Enter instead of Enter.


Regards!

 

[NARAYANK991]

Hi Suresh ,


So also will :


=AVERAGE(IF((A1:A6<STDEV(A1:A6)),(A1:A6)))


entered as an array formula , using CTRL SHIFT ENTER.


Narayan

 

[SirJB7]

Hi, ssuresh98!

Go with NARAYANK991 formula, much elegant.

Regards!


@NARAYANK991

Hi!

That was what I looked for and didn't find

Regards!

 

[ssuresh98]

Works beautifully. Thanks so much Narayan. And thanks to SirBJ7 too.

 

[SirJB7]

Hi, !

Glad to help. All credit to NARAYANK991. Thanks for your feedback and Welcome back whenever needed or wanted.

Regards!

 

[ssuresh98]

Can you please explain how it works? I don't see a cutoff for the SD. Also it does not work if there are blank values, can this be overcome? TIA.