Searching a range of values for outliers

**kurifodo** · 05-21-2011, 08:45 PM

Hi!! I've been trying with no luck to come up with a way to:

a) find all the outliers in a range of a 1000 rows. The criteria is simply +/- average*2. The outlier can either be above or below.
b) colour the background of these outliers.
c) repeat a&b in the next column.

So in essence, I'm trying to find all values in a table that are twice (positive or negative) that of the average and highlight them.

Example:
A, B
4, 1
4, 1
4, 1
4, 4
4, 1
4, 1
4, 1
12, 1

In this case the outlier for A is 12 and the outlier for B is 4.

Any help, be it in a formula for a given cell, or VBA, is deeply appreciated.

Sincerely
Kurifodo

**abousetta** · 05-21-2011, 10:06 PM

Hello and welcome to the forum,

Just for clarification, you mean the average (mean * 2) without the value we are checking because if you include the outlier as in your example (A8) is not an outlier. Second do you want an answer without vba? Off the top of my head, I see this a vba question, but others may be able to put together a forumla to solve this problem.

abousetta

**teylyn** · 05-21-2011, 10:11 PM

Hello,

The criteria is simply +/- average*2.

the result of this calculation applied to all values in column A is 10 (given your data sample)

12 is not twice of that.

The median of all the numbers multiplied by 2 is 8. 12 is also not twice of that.

Applying both Average()*2 and Median()*2 would not identify 12 as an outlier. So we need a better way to identify 12 as an outlier. Once that's nailed down, the formula can be used in conditional formatting.

**kurifodo** · 05-22-2011, 09:04 AM

what i meant is using =average(A1:A8). this results in 5, thus average*2 is 10. since 10<12 it should be classed as an outliers. For B it is the same principal. =average(B1:B8). where the result is 1.375, thus average*2 = 2.75, this makes makes 4 an outlier. A & B are completely separate from each other. thus if the answer needs to be in VBA, then that's fine, if it can be implemented as a formula, that's also great. Hopefully I've explained it a bit better

Thank you very much for trying to help so far, it's appreciated.

Kurifodo

**teylyn** · 05-22-2011, 06:20 PM

Yeah, well, I guess in real life, your outlier values will be spread among the non-outliers, so the data will more likely look like

A, B
4, 1
4, 1
4, 1
4, 4
12, 1
4, 1
4, 1
4, 1

with the outlier values tucked safely amongst the rest of them. The challenge is to come up with something that identifies them as outliers when you don't know which cells they're in. So, how do you identify the cells that are allowed to contribute to the average or median?

Are you prepared to sort your data by value?