Automatically checking for outliers in the data series

[nmusoke17]

I have analysed revenue collections over three years by location. However, I am observing some wide swings in the data. What Excel formula can I use to help me automatically show me that the data has outliers in it.

Thank you

 

[Cubist]

There are various methods to define outliers. What do you consider as "outliers"?

 

[nmusoke17]

There are various methods to define outliers. What do you consider as "outliers"?

for example, if I have three figures over three years such 4, 7 and 2000. I would consider 2000 as an outlier

 

[Cubist]

It's obvious in that example, but what about something closer like
1,2,3,4,5,10,20,1,1,1,1,1,1,8

Do you consider 8 as outliers? What I'm getting at is what is the cut-off for it to be an outlier?

 

[nmusoke17]

It's obvious in that example, but what about something closer like
1,2,3,4,5,10,20,1,1,1,1,1,1,8

Do you consider 8 as outliers? What I'm getting at is what is the cut-off for it to be an outlier?

in that regard i would consider 20 as an outlier, basically any change of more than 100%

 

[Cubist]

This uses the 3 standard deviation rule. See if this works for you.

Book1
	A
2	52.48
3	49.31
4	53.24
5	57.62
6	48.83
7	48.83
8	57.9
9	53.84
10	47.65
11	52.71
12	47.68
13	47.67
14	51.21
15	40.43
16	41.38
17	47.19
18	44.94
19	51.57
20	45.46
21	42.94
22	57.33
23	48.87
24	50.34
25	42.88
26	47.28
27	50.55
28	44.25
29	51.88
30	47
31	48.54
32	100
33	59.26
34	49.93
35	44.71
36	54.11
37	43.9
38	51.04
39	40.2
40	43.36
41	50.98
42	53.69
43	50.86
44	49.42
45	48.49
46	42.61
47	46.4
48	47.7
49	55.29
50	51.72
51	41.18
52	20
53	100
54	120
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A54	Expression	=A2>AVERAGE($A$2:$A$54)+3*STDEV.S($A$2:$A$54)	text	NO