Array equations:
Array equations are very useful when working with data that are in
category and value configuration. For example:
Step 1: To extract unique values from the parameter column, use the Advanced
Filter:
Select ‘Copy to another location”, fill in appropriate values
in the ‘List range’ and ‘Copy to’, and check the
‘Unique records only’ checkbox.
Next, to compute – for example – percentile use following
equation:
Enter equation similar to above for your
data. Do not manually type in the curly brackets in the beginning and the
end, those show up automatically if – after typing in your equation
– you hit {Ctrl}+{Shift}+{Enter}. Doing so will make the equation an
array equation.
In the above equation, A2:A20 will be evaluated for match
with value from E2 and if match is found corresponding values from B2:B20 are
returned otherwise ‘False’ is returned. These returned values are
then used to calculate the ‘percentile’ function that surrounds
the ‘if’ function. Use relative (without $) and absolute ranges
(with $) properly to make coping of equation errorfree.
You can do Min, Max, Average, Stdev etc
instead of percentile to calculate desired statistic.
Box and Whisker plot in Excel
Step 1. Compute statistics
For each of your data set:
 If data is in category and
value list then get unique categorical values using the advance filer
technique shown above.
 Also do copy à paste special à transpose to get the
values in columns instead of rows
 Calculate 25th, 50th, and 75th
percentile.
 Use Excel's percentile
function to calculate your percentiles
 For small dataset,
Excel’s percentile function’s return value doesn’t
match minitab’s value. VTools uses method similar to Minitab to
calculate percentiles for the boxplot
 Calculate Min and Max
 Use Excel's min and max
function
 Calculate Inter Quartile Range
 IQR = 75th percentile 
25th percentile
 Calculate upper and lower
whiskers
 upper whisker = lesser of
max and [75th + 1.5 * IQR]
 Note: Technically, upper
whisker should extend only up to the highest data point within 75^{th}
+ 1.5*IQR
 lower whisker = greater
of min and [25th  1.5 * IQR]
 Note: Technically, upper
whisker should extend only up to the lowest data point within 25^{th}
 1.5*IQR
Step 2: Plot
Method #1: Stacked bar chart
We can fake stacked bar chart to
look like a box plot. Since bars are stacked on each other, we need to set
data up so that 25%, 50%, and 75% are at the right location. See the
following example:
Next do the stacked column chart
using the newly created 25%, 50% and 75% values. Graph should look similar to
this:
Next we add Error Bars:
Click on the 25% series, then
click on the Error Bars under chart Tools à
Layout à Error Bars à More Error Bar Options…
Then choose Minus under display
and Custom under Error Amount. Click on specify Value and select lower
whisker values for negative Error Values.
Repeat this for 75% and add Plus
error bars and choose upper whisker values for Positive Error Values.
Your graph should now look like this:
Next format the 25% data series
and set fill to no fill and set border color to no line. With a bit more
clean up your chart should look like below:
This approach breaks down if any
negative values are encountered. There are several ways to combat
negative values
1.
Stack positive and negative values separately.
a.
That is too much work for manually generating charts.
b.
This is how VTools versions 4+ create boxplot.
2.
Do a data offset to bring everything into positive territory. Plot
everything on secondary axes and make primary axes offset by same amount as
data.
3.
Do a line chart as described below.
Method #2 Line chart: Arrange the
data so that it resembles box & whisker when plotted as a line graph
One way to arrange data could
be:

X

Y

0.9

lower whisker

1.1

lower whisker

1.0

lower whisker

1.0

25th percentile

0.8

25th percentile

0.8

75th percentile

1.0

75th percentile

1.0

upper whisker

0.9

upper whisker

e1.1

upper whisker

1.0

upper whisker

1.0

75th percentile

1.2

75th percentile

1.2

25th percentile

0.8

25th percentile

0.8

50th percentile

1.2

50th percentile

Plot this data as line chart and
you will have box plot!
Why do all this yourself when VTools can do this for you? Download VTools
Create Normal Probability Plot in Excel:
For each of your data set:
 Sort data ascending
 Assign rank to your data. rank
= 1 for the lowest data point and rank = n for the highest data point
 Create an XY (Scatter) graph
with your data on the XAxis and it's ZScore on the YAxis
 ZScore = NORMSINV((rank
 0.5) / n)
Why do all this yourself when VTools can do this for you? Download VTools
Create Cumulative Percent Plot (S Curve) in Excel:
 Create two new columns.
 In the first column enter
Xaxis title in the first row and 1 through 100 in the following rows
 In the second column, enter
Yaxis title followed by the percentile values from the corresponding
row of the first column.
 use Excel's percentile
function
 Plot these two column as an XY
(Scatter) graph in Excel
Why do all this yourself when VTools can do this for you? Download VTools
