           ...Excel add-in for quick data analysis     VTools - Tips

# Excel Tips & Tricks

## 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 error-free.

## Box and Whisker plot in Excel

Step 1. Compute statistics

For each of your data set:

1. 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
2. Calculate 25th, 50th, and 75th percentile.
• Use Excel's percentile function to calculate your percentiles
1. 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
3. Calculate Min and Max
• Use Excel's min and max function
4. Calculate Inter Quartile Range
• IQR = 75th percentile - 25th percentile
5. Calculate upper and lower whiskers
• upper whisker = lesser of max and [75th + 1.5 * IQR]
1. Note: Technically, upper whisker should extend only up to the highest data point within 75th + 1.5*IQR
• lower whisker = greater of min and [25th - 1.5 * IQR]
1. Note: Technically, upper whisker should extend only up to the lowest data point within 25th - 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 off-set 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!

### Create Normal Probability Plot in Excel:

For each of your data set:

1. Sort data ascending
2. Assign rank to your data. rank = 1 for the lowest data point and rank = n for the highest data point
3. Create an XY (Scatter) graph with your data on the X-Axis and it's Z-Score on the Y-Axis
• Z-Score = NORMSINV((rank - 0.5) / n)

### Create Cumulative Percent Plot (S Curve) in Excel:

1. Create two new columns.
2. In the first column enter X-axis title in the first row and 1 through 100 in the following rows
3. In the second column, enter Y-axis title followed by the percentile values from the corresponding row of the first column.
• use Excel's percentile function
4. 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  Aug 1, 2017 VTools version 4.20 is released. Download your copy from the download page.   Support VTools by purchasing your copy today. Purchase VTools   Last updated: Oct 15, 2012