A few different ways come to mind. Here's one.
Suppose the plot data are:
9
1.467435663
1.111832123
-10
-0.038088117
-1.178219519
1.486172227
-1.176408602
1.424129173
-0.619291165
1.941597437
-1.203311471
0.774216042
0.769795668
-4
-1.047014577
1.075195355
0.546741368
-0.783876553
18
0.831463101
To a person it would be 'obvious' that the 2 positive outliers are 9
and 18, the two negatives are -10 and -4. But, that which is obvious
to you and me is not necessarily so for a computer.
The task breaks down into two large pieces. The first is to decide
what to plot. The second is to create the arrows.
Suppose the above data set is in B1:B21.
First, we need to define an outlier. In my test, I used the algorithm
that the absolute value had to be greater than the average of the abs
value of all the observations. So, in C1:C21 we will indicate a
positive outlier with a +1, a negative outlier with a -1, and a non-
outlier with a zero. Select C1 and enter the array formula
=IF(ABS(B1)>AVERAGE(IF($B$1:$B$21<0,-$B$1:$B$21,$B$1:$B$21)),IF(B1>
AVERAGE($B$1:$B$21),1,-1),0)
Note that we need this convoluted check for absolute since the ABS
function works with only a single argument.
Copy C1 to C2:C21.
Next, we need to get the data points that are not outliers. That is in
D. Select D1
21 and enter the array formula =IF(C1:C21=0,B1:B21,NA())
Note that you could also enter a normal formula =IF(C1=0,B1,NA())in D1
and copy to D2
21.
Next, we need to decide what value to plot for the positive outliers.
I decided to use the maximum of the non-outliers. So, select E1:E21
and enter the array formula
=IF(C1:C21=1,MAX(IF(NOT(ISNA(D1
21)),D1
21)),NA()) Again, as for
col. D, you could do this without an array formula.
Next, we need to decide what value to plot for the negative outliers.
I decided to use the minimum of the non-outliers. So, select F1:F21
and enter the array formula
=IF(C1:C21=-1,MIN(IF(NOT(ISNA(D1
21)),D1
21)),NA()) Again, as for
col. D or E, you could do this without an array formula.
The final step in the first phase of this task is to create a composite
data set that includes the non-outliers and the adjusted positive and
negative outliers. So, in G1 enter =IF(ISNA(D1),IF(ISNA(E1),F1,E1),D1)
and copy G1 to G2:G21.
Now, plot E:G as three series using a XY Scatter chart. It might be
easiest to plot just G then add E and F. But, do it however makes you
comfortable. Just make sure that XL doesn't consider any of the series
as the x-values.
For the series corresponding to G set the Line to Automatic (in the
chart, double-click one of the plotted data points and select the
Patterns tab).
On to the 2nd part, creating the arrows.
From the Drawing toolbar create two vertical arrows, one pointing up
the other down. Make them an aesthetically appealing size (I used
0.26"). Change the color if desired. I used green for the up arrow
and red for the down arrow.
Select the up-arrow and copy it. Select the positive outlier series
(corresponding to column E). Paste.
Select the down-arrow and copy it. Select the negative outlier series
(corresponding to column F). Paste.
Note that the first variant of how to plot this kind of data is
inherent in the building blocks above. If you don't want the adjusted
outliers shown as though part of the plotted data, plot D:F rather than
E:G.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
