Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Sparklines / Add Sparklines using Formulas / BoxPlot Sparkline
In This Topic
    BoxPlot Sparkline
    In This Topic

    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:

    • 0: the whisker is a line and outlier is a circle.
    • 1: the whisker is a rectangle and outlier is a line.

    The default value is 0 (Classical).

    vertical

    Optional

    A boolean that represents whether to display the sparkline vertically.

    The default value is FALSE.

    Usage Scenario

    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)"
    

    Using the Spread Designer

    1. Type data in a cell or a column or row of cells in the designer.
    2. Select a cell for the sparkline.
    3. Select the Insert menu.
    4. Select a sparkline type.
    5. Set the Data Range in the Create Sparklines dialog (such as =Sheet1!$E$1:$E$3).
      Alternatively, set the range by selecting the cells in the range using the pointer.

      You can also set additional sparkline settings in the dialog if available.

    6. Select OK.
    7. Select Apply and Exit from the File menu to save your changes and close the designer.