A boxplot sparkline uses quartiles to display data and gives you a good indication of how the values in the data are spread out. It is a quick way of examining data sets graphically.
Box plots are useful as they provide a visual summary of the data enabling researchers to quickly identify mean values, the dispersion of the data set, and signs of skews.
The following image displays the sparkline areas.
The boxplot sparkline formula has the following syntax:
=BOXPLOTSPARKLINE(points, [boxPlotClass, showAverage, scaleStart, scaleEnd, acceptableStart, acceptableEnd, colorScheme, style, vertical])
The formula options are described below:
| Option | Description |
| points | A reference that represents the cell range that contains the values, such as "A1:A4". |
|
boxPlotClass Optional |
Q1 = 25% percentile, Q3 = 75% percentile, IQR (interquartile range) = Q3 - Q1. |
| 5ns (default): Whisker ends at minimum and maximum, median, no outliers. | |
| 7ns: Whisker ends at 2% percentile and 98% percentile, hatch marks at 9% percentile and 91% percentile, outliers beyond 2% percentile and 98% percentile. | |
| tukey: Whisker ends at a value (the minimum of the points between Q1 and Q1 - 1.5*IQR, use the point if it exists or use the minimum) and a value (the maximum of the points between Q3 and Q3 + 1.5 * IQR, use the point if it exists or use the maximum), outliers beyond Q1 - 1.5*IQR and Q3 + 1.5 * IQR, and extreme outliers beyond Q1 - 3 * IQR and Q3 + 3 * IQR. | |
| bowley: Whisker ends at minimum and maximum, hatch marks at 10% percentile and 90% percentile, no outliers. | |
| sigma3: Whisker ends at a value (average - 2 * StDev > scaleStart ? average - 2 * StDev : minimum) and a value (average + 2 * StDev < scaleEnd ? average = 2 * StDev : maximum), box at average +/- stdev, outliers beyond average - 2 * StDev and average + 2 * StDev, and extreme outliers beyond average - 3 * StDev and average + 3 * StDev. | |
|
showAverage Optional |
A boolean that represents whether to show the average. The default value is FALSE. |
|
scaleStart Optional |
A number or reference that represents the minimum boundary of the sparkline, such as 1 or "A6". The default value is the minimum of all values. |
|
scaleEnd Optional |
A number or reference that represents the maximum boundary of the sparkline, such as 8 or "A7". The default value is the maximum of all values. |
|
acceptableStart Optional |
A number or reference that represents the start of the acceptable line, such as 3 or "A8". The default value is None. |
|
acceptableEnd Optional |
A number or reference represents the end of the acceptable line, such as 5 or "A9". The default value is None. |
|
colorScheme Optional |
A string that represents the color of the sparkline's box. The default value is "#D2D2D2". |
|
style Optional |
A number or reference that represents the sparkline style. The style can be 0 or 1:
The default value is 0 (Classical). |
|
vertical Optional |
A boolean that represents whether to display the sparkline vertically. The default value is FALSE. |
Consider a scenario where a company, for example, Mescius wants to visualize the download count of different Spread products available throughout the year. A boxplot sparkline helps display the visual summary of a fiscal year in terms of the product downloads.
| C# |
Copy Code
|
|---|---|
// Get sheet var worksheet = fpSpread1.Sheets[0].AsWorksheet(); // Set data worksheet.SetValue(1, 0, new object[,] { {"Products", "Apr' 20","May' 20","Jun' 20","Jul' 20","Aug' 20","Sep' 20","Oct' 20","Nov' 20","Dec' 20","Jan' 21","Feb' 21","Mar'21","Diagram"}, {"Spread.NET",43340,20200,40188,83762,13112,34543,56756,23434,45022,70028,63098,72690,null}, {"SpreadJS", 92887,73289,93876,80002,93200,98867,10507,63423,71881,81367,60197,90012,null}, {"Spread COM", 1292,3411,565,1002,915,1301,451,891,505,537,791,618,null}, {"DataViewsJS",2376,1235,5241,4234,5235,8234,9102,7016,3432,1922,1840,2560,null} }); // Set formula for boxplotsparkline worksheet.Cells["N3"].Formula = "BOXPLOTSPARKLINE(B3:M3,\"7ns\",TRUE,0,100000,5000,10000,\"#F58624\",0,FALSE)"; worksheet.Cells["N4"].Formula = "BOXPLOTSPARKLINE(B4:M4,\"5ns\",TRUE,0,100000,2000,80000,\"#F58624\",0,FALSE)"; worksheet.Cells["N5"].Formula = "BOXPLOTSPARKLINE(B5:M5,\"sigma3\",TRUE,0,2000,100,1000,\"#F58624\",0,FALSE)"; worksheet.Cells["N6"].Formula = "BOXPLOTSPARKLINE(B6:M6,\"bowley\",TRUE,0,10000,100,5000,\"#F58624\",0,FALSE)"; |
|
| Visual Basic |
Copy Code
|
|---|---|
'Get sheet Dim worksheet = FpSpread1_Sheet1.AsWorksheet() 'Set data worksheet.SetValue(1, 0, New Object(,) { {"Products", "Apr' 20", "May' 20", "Jun' 20", "Jul' 20", "Aug' 20", "Sep' 20", "Oct' 20", "Nov' 20", "Dec' 20", "Jan' 21", "Feb' 21", "Mar'21", "Diagram"}, {"Spread.NET", 43340, 20200, 40188, 83762, 13112, 34543, 56756, 23434, 45022, 70028, 63098, 72690, Nothing}, {"SpreadJS", 92887, 73289, 93876, 80002, 93200, 98867, 10507, 63423, 71881, 81367, 60197, 90012, Nothing}, {"Spread COM", 1292, 3411, 565, 1002, 915, 1301, 451, 891, 505, 537, 791, 618, Nothing}, {"DataViewsJS", 2376, 1235, 5241, 4234, 5235, 8234, 9102, 7016, 3432, 1922, 1840, 2560, Nothing} }) 'Set formula for BoxPlotSparkline worksheet.Cells("N3").Formula = "BOXPLOTSPARKLINE(B3:M3,""7ns"",TRUE,0,100000,5000,10000,""#F58624"",0,FALSE)" worksheet.Cells("N4").Formula = "BOXPLOTSPARKLINE(B4:M4,""5ns"",TRUE,0,100000,2000,80000,""#F58624"",0,FALSE)" worksheet.Cells("N5").Formula = "BOXPLOTSPARKLINE(B5:M5,""sigma3"",TRUE,0,2000,100,1000,""#F58624"",0,FALSE)" worksheet.Cells("N6").Formula = "BOXPLOTSPARKLINE(B6:M6,""bowley"",TRUE,0,10000,100,5000,""#F58624"",0,FALSE)" |
|
You can also set additional sparkline settings in the dialog if available.
