Formula to locate and remove outlying values from a large data set

[placidbomb]

Hi guys,

Am relatively new to excel and although I do have some experience with using formula, this is a scenario that I can't seem to be able to crack. I have a set of data, where I need to identify and remove outlying values from the data set. These outlying values are usually too small or too big causing the average to skew by a lot. I am trying to avoid that. In the attached sample data sheet I have manually selected the values (highlighted in blue) that I want to remove in real life scenario. But I would like to know how I can remove those outlying values if the data set was very large. In real scenario, I won't have the time or the accuracy to locate these outlying values one by one since I will have hundreds of columns. Also not required, but it would be helpful if there was a way to close the empty cell gaps, once those outlying values have been deleted. Thank you very much.

 

[hagia_sofia]

Hello, here is an option where you need to pick a range and set lower (50 at the moment) and upper (200 at the moment) bounds:

=LET(
a,A1:V100,
lower,50,
upper,200,
b,IF((a="")+(a>upper)+(a<lower)=0,a,""),
IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(a)),LAMBDA(x,y,HSTACK(x,FILTER(CHOOSECOLS(b,y),CHOOSECOLS(b,y)<>"")))),,1),""))

No idea how it will perform on a larger set of data but you could test it (are there any empty columns within the data?)...

 

[placidbomb]

Hi Sofia. Thanks. So is this formula for one single column? Also to answer your question, no there won't be any empty columns within the data.

 

[hagia_sofia]

Hi Sofia. Thanks. So is this formula for one single column? Also to answer your question, no there won't be any empty columns within the data.

It could be used for one single column but you could also select multiple columns as in the example above, i.e. the A1:V100 range.