Kition
sheetFormulas & Functions

Exam Score Statistics

A class's test scores described with AVERAGE, MEDIAN, MODE, STDEV, QUARTILE and PERCENTILE, binned with FREQUENCY into a histogram.

Exam Score Statistics — Sheet sample rendered by Kition

Twenty-four scores from a biology mid-term. Each student gets a rank (RANK.EQ), a z-score (STANDARDIZE against the class mean and standard deviation) and a letter grade looked up with XLOOKUP in next-smaller mode. The panel on the right is the usual descriptive set: count, mean, median, mode, sample standard deviation, range, quartiles, the 90th percentile and the pass rate. FREQUENCY bins the scores in one spilling formula (DROP removes its overflow bin), and the column chart of the bins is the histogram.

Steps

52 commands
showcase.yaml114 lines
steps:
  - { command: range.setValues, params: { range: A1, values: [["Biology Mid-Term · Score Analysis"], ["Class 10B · 24 students · out of 100"]] } }
  - command: range.setValues
    params:
      range: A4
      values:
        - [Student, Score, Rank, z-score, Grade]
        - [Aiden, 77, "=RANK.EQ(B5,$B$5:$B$28)", "=STANDARDIZE(B5,$H$6,$H$9)", "=XLOOKUP(B5,$K$19:$K$23,$L$19:$L$23,,-1)"]
        - [Bea, 90]
        - [Carlos, 66]
        - [Dana, 87]
        - [Elif, 73]
        - [Femi, 73]
        - [Gia, 97]
        - [Hugo, 78]
        - [Isla, 76]
        - [Jun, 84]
        - [Kira, 88]
        - [Liam, 76]
        - [Maya, 82]
        - [Nico, 65]
        - [Olu, 72]
        - [Pia, 71]
        - [Quinn, 61]
        - [Rosa, 59]
        - [Sami, 58]
        - [Tara, 73]
        - [Uma, 74]
        - [Vik, 72]
        - [Wren, 77]
        - [Yara, 61]
  - { command: edit.fillDown, params: { range: "C5:E28" } }
  - command: range.setValues
    params:
      range: G4
      values:
        - [Statistic, Value]
        - [Students, "=COUNT(B5:B28)"]
        - [Mean, "=AVERAGE(B5:B28)"]
        - [Median, "=MEDIAN(B5:B28)"]
        - [Mode, "=MODE.SNGL(B5:B28)"]
        - [Standard deviation, "=STDEV.S(B5:B28)"]
        - [Lowest, "=MIN(B5:B28)"]
        - [Highest, "=MAX(B5:B28)"]
        - [Lower quartile, "=QUARTILE.INC(B5:B28,1)"]
        - [Upper quartile, "=QUARTILE.INC(B5:B28,3)"]
        - [90th percentile, "=PERCENTILE.INC(B5:B28,0.9)"]
        - [Pass rate (60+), "=COUNTIF(B5:B28,\">=60\")/H5"]
  - command: range.setValues
    params:
      range: G18
      values:
        - [Score band, Students, Up to]
        - [Below 60, "=DROP(FREQUENCY(B5:B28,I19:I23),-1)", 59]
        - [60 to 69, null, 69]
        - [70 to 79, null, 79]
        - [80 to 89, null, 89]
        - [90 to 100, null, 100]
  - command: range.setValues
    params:
      range: K18
      values:
        - [From, Grade]
        - [0, F]
        - [60, D]
        - [70, C]
        - [80, B]
        - [90, A]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#065f46" } }
  - { command: home.fontColor, params: { range: A2, color: "#6b7280" } }
  - { command: home.fillColor, params: { range: "A4:E4", color: "#065f46" } }
  - { command: home.fillColor, params: { range: "G4:H4", color: "#065f46" } }
  - { command: home.fillColor, params: { range: "G18:I18", color: "#065f46" } }
  - { command: home.fillColor, params: { range: "K18:L18", color: "#6b7280" } }
  - { command: home.fontColor, params: { range: "A4:L4", color: "#ffffff" } }
  - { command: home.fontColor, params: { range: "G18:L18", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A4:L4" } }
  - { command: home.bold, params: { range: "G18:L18" } }
  - { command: home.alignCenter, params: { range: "B4:E28" } }
  - { command: home.alignRight, params: { range: "H4" } }
  - { command: home.numberFormat, params: { range: "D5:D28", format: "+0.00;-0.00;0.00" } }
  - { command: home.bold, params: { range: "E5:E28" } }
  - { command: home.borders, params: { range: "A5:E28", preset: insideHorizontal, color: "#e5e7eb" } }
  - { command: home.conditionalFormat, params: { range: "D5:D28", rule: { type: colorScale, colors: ["#f87171", "#ffffff", "#34d399"] } } }
  - { command: home.conditionalFormat, params: { range: "B5:B28", rule: { type: expression, formula: "=B5<60", preset: lightRedFillDarkRedText } } }
  - { command: home.numberFormat, params: { range: "H6:H7", format: "0.0" } }
  - { command: home.numberFormat, params: { range: "H9", format: "0.00" } }
  - { command: home.numberFormat, params: { range: "H12:H14", format: "0.0#" } }
  - { command: home.numberFormat, params: { range: "H15", format: "0.0%" } }
  - { command: home.borders, params: { range: "G5:H15", preset: insideHorizontal, color: "#e5e7eb" } }
  - { command: home.borders, params: { range: "G15:H15", preset: bottom, color: "#065f46" } }
  - { command: home.fillColor, params: { range: "G6:H6", color: "#ecfdf5" } }
  - { command: home.bold, params: { range: "G6:H6" } }
  - { command: home.alignCenter, params: { range: "H18:I23" } }
  - { command: home.fontColor, params: { range: "I19:I23", color: "#6b7280" } }
  - { command: home.alignCenter, params: { range: "K18:L23" } }
  - { command: home.fontColor, params: { range: "K19:L23", color: "#4b5563" } }
  - { command: home.borders, params: { range: "G19:I23", preset: insideHorizontal, color: "#e5e7eb" } }
  - { command: home.borders, params: { range: "K18:L23", preset: outside, color: "#9ca3af" } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 70 } }
  - { command: home.columnWidth, params: { cols: "B:E", width: 54 } }
  - { command: home.columnWidth, params: { cols: "F:F", width: 24 } }
  - { command: home.columnWidth, params: { cols: "G:G", width: 118 } }
  - { command: home.columnWidth, params: { cols: "H:I", width: 60 } }
  - { command: home.columnWidth, params: { cols: "J:J", width: 24 } }
  - { command: home.columnWidth, params: { cols: "K:L", width: 48 } }
  - { command: insert.chart, params: { range: "G18:H23", type: column }, as: chart }
  - { command: chart.set, params: { title: Score distribution, legend: none, dataLabels: true, at: J4, width: 330, height: 250 } }
  - { command: chart.formatSelection, params: { series: 0, color: "#10b981" } }
  - { command: sheet.read, params: { range: "A5:E10" }, as: roster }
  - { command: sheet.read, params: { range: "H5:H15" }, as: stats }
  - { command: sheet.read, params: { range: "H19:H24" }, as: bins }

Checks

5 checks on every build
  • results.roster.values = [["Aiden",77,8,{"approx":0.2400114,"tol":0.000001},"C"],["Bea",90,2,{"approx":1.5311074,"tol":0.000001},"A"],["Carlos",66,19,{"approx":-0.8524544,"tol":0.000…
  • results.stats.values = [[24],[{"approx":74.5833333,"tol":0.000001}],[73.5],[73],[{"approx":10.0689651,"tol":0.000001}],[58],[97],[69.75],[79],[{"approx":87.7,"tol":0.000001}],[{"ap…
  • results.bins.values = [[2],[4],[12],[4],[2],[null]]
  • Diagnostics: none, the kit's own check finds nothing wrong
  • The saved document opens again, and its thumbnail matches thumb.png within the render tolerance

Related

More Sheet samples
sheetFormulas & Functions

Dynamic Array Event Report

Three spilling summaries of a conference sign-up list, built with UNIQUE, SORT, FILTER, SORTBY, CHOOSECOLS and TAKE.

1 sheet; used range A1:I23
sheetFormulas & Functions

SUMIFS Regional Sales Summary

A quarter's deal log rolled up by region and product with SUMIFS, COUNTIFS, AVERAGEIFS and MAXIFS.

1 sheet; used range A1:M28
sheetFormulas & Functions

Text Cleanup with Functions

A messy contact import tidied with TRIM, PROPER, TEXTBEFORE, TEXTAFTER, TEXTJOIN and a LET formula that rebuilds every phone number.

1 sheet; used range A1:H17
sheetFormulas & Functions

Invoice Due-Date Tracker

Due dates that skip weekends and office holidays with WORKDAY, business days with NETWORKDAYS, and a status for every invoice.

1 sheet; used range A1:M19