Computing desk
< July 31	<< Jul \| August \| Sep >>	Current desk >

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

August 1

Obscure Excel function

I'm looking for a specific Excel function, and I'm having difficulty finding it, probably because it doesn't exist. Is there an Excel function that will calculate the average of a group of cells, determine which values are more than two standard deviations away from the mean, remove those values, and then calculate the new average of the remaining values? If no such function exists, what could I type into a cell to make it do that? --Puzzledvegetable^{Is it teatime already?} 17:38, 1 August 2019 (UTC)[reply]

@Puzzledvegetable: There is a list of statistical functions for Excel here: [1]. Some combinations of these might do what you want, perhaps using the AVERAGEIFS and STDEV.S functions? RudolfRed (talk) 17:55, 1 August 2019 (UTC)[reply]

Note that you would need to decide whether such a function should act recursively. That is, you may find that using this new average, additional cells are now outside the 2 standard deviation limit, and if you remove those, and adjust the average again, the same thing happens again, etc. SinisterLefty (talk) 03:02, 2 August 2019 (UTC)[reply]

I haven't seen such a function. The closest I've seen is the trimmean function, which trims X% of the values that are farthest from the mean. 199.164.8.1 (talk) 12:03, 2 August 2019 (UTC)[reply]

I don't know anything about Excel, but that might do it. Trim the 5% of the values that are fartherest from the mean. That won't always be the ones that are more than 2 S.D., but it should be close. Bubba73 ^{You talkin' to me?} 05:32, 3 August 2019 (UTC)[reply]

It depends entirely on the distribution. It is easy to have all values be less than 2SD from the mean. Then, trimmean will trim values that are not outliers. 135.84.167.41 (talk) 11:56, 5 August 2019 (UTC)[reply]

The Excel article says that it can be programmed with Visual Basic for Applications. It should be a simple matter to write a routine to do that. Bubba73 ^{You talkin' to me?} 19:08, 5 August 2019 (UTC)[reply]