Excel Tips

create Box and Whisker plot in Excel

For each of your data set:

1. Calculate 25th, 50th, and 75th percentile.
   • Use Excel's percentile function to calculate your percentiles
2. Calculate Min and Max
   • Use Excel's min and max function
3. Calculate Inter Quartile Range
• IQR = 75th percentile - 25th percentile
4. Calculate upper and lower whiskers
• upper whisker = lesser of max and [75th + 1.5 * IQR]
• lower whisker = greater of min and [25th - 1.5 * IQR]
5. 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
   1.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

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:

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)

Why do all this yourself when VTools can do this for you? Download VTools

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