Score Distribution Analysis

Analyze test score distributions using histograms and visualize with Google Sheets charts.

User Story

You administer a certification exam and want to understand the score distribution: - What score ranges have the most test takers? - What percentile is each score at? - How many students passed vs. failed (cutoff at 70)?

You have a Google Sheet with exam scores:

student_id	name	score
1	Alice	85
2	Bob	62
3	Carol	91
4	David	75
5	Eve	88
6	Frank	58
...	...	...

You need to:

• Bin scores into ranges (50-60, 60-70, 70-80, 80-90, 90-100)
• Count students in each bin
• Calculate percentiles (relative and cumulative frequency)
• Create a visual histogram to share with stakeholders
• Identify outliers and edge cases (underflow: score < 50, overflow: score ≥ 100)

With gaslamp

JavaScript

function cookbook_scoreDistribution() {
  // Step 1: Load and validate exam scores
  const schema = {
    student_id: gaslamp.FlameGuards.isNumber,
    name:       gaslamp.FlameGuards.isString,
    score:      gaslamp.FlameGuards.isNumber,
  };

  const sourceSheet = SpreadsheetApp.getActiveSheet();
  const workbook    = SpreadsheetApp.getActiveSpreadsheet();

  const df = gaslamp.BareFrame.fromSheet(sourceSheet);
  const { passed, failed } = gaslamp.FlameFrame.from(df, schema);

  if (failed.length > 0) {
    console.error(failed.length + ' validation error(s) found:');
    console.error(failed.display());
    return;
  }

  console.log(`Loaded ${passed.length} exam scores`);

  // Step 2: Create histogram using ForgeFrame
  // Bin scores into 10 ranges from 0 to 100
  const histogram = gaslamp.ForgeFrame.from(passed).h1('score', {
    nbins: 10,
    xlow: 0,
    xup: 100,
  });

  console.log('Score distribution:');
  console.log(histogram.display());

  // Step 3: Write histogram to sheet for visualization
  const histogramSheet = workbook.getSheetByName('Score Histogram')
    ?? workbook.insertSheet('Score Histogram');

  histogram.toSheet(histogramSheet);

  // Step 4: Create a bar chart from the histogram data
  const chart = histogramSheet.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(histogramSheet.getDataRange())
    .setOption('title', 'Exam Score Distribution')
    .setOption('hAxis.title', 'Score Range')
    .setOption('vAxis.title', 'Number of Students')
    .setOption('legend.position', 'none')
    .setPosition(2, 5)
    .build();

  histogramSheet.insertChart(chart);

  // Step 5: Filter histogram to find key statistics
  const passRate = histogram
    .filter((row) => row.get('bin').includes('[70') || row.get('bin').includes('[80') || row.get('bin').includes('[90'));

  const passCount = passRate.toRows().reduce((sum, row) => sum + row.get('count'), 0);
  const passPercentage = (passCount / passed.length * 100).toFixed(1);

  console.log(`Pass rate (score ≥ 70): ${passPercentage}%`);

  // Step 6: Export detailed statistics
  const statsData = gaslamp.BareFrame.fromArrays([
    ['Metric', 'Value'],
    ['Total Exam Takers', passed.length],
    ['Passing Score (≥70)', passCount],
    ['Pass Rate (%)', passPercentage],
    ['Failing Count', passed.length - passCount],
    ['Mean Score', (passed.toRows().reduce((sum, row) => sum + row.get('score'), 0) / passed.length).toFixed(2)],
  ]);

  const statsSheet = workbook.getSheetByName('Exam Statistics')
    ?? workbook.insertSheet('Exam Statistics');

  statsData.toSheet(statsSheet);

  console.log('Score distribution analysis complete');
}

Key gaslamp features used:

• .h1() histogram method — bin continuous scores into discrete ranges with automatic count, relative frequency, and cumulative stats
• ForgeFrame.from() — statistical analysis wrapper that returns row-oriented results (one row per bin)
• .toSheet() integration — histogram output writes directly to Google Sheets (no manual range/cell operations)
• Google Sheets charting — use newChart() to create visual histograms from histogram data
• Post-histogram filtering — use standard .filter() on histogram results to calculate pass rates and other derived metrics
• Direct calculation — combine histogram with .reduce() for percentile and aggregation calculations

Without gaslamp

JavaScript

function cookbook_scoreDistribution_vanilla() {
  const sourceSheet = SpreadsheetApp.getActiveSheet();
  const workbook    = SpreadsheetApp.getActiveSpreadsheet();

  // Step 1: Load and validate exam scores
  const range = sourceSheet.getDataRange();
  const values = range.getValues();
  const headers = values[0];
  const rows = values.slice(1);

  const studentIdIdx = headers.indexOf('student_id');
  const nameIdx = headers.indexOf('name');
  const scoreIdx = headers.indexOf('score');

  const validated = [];
  for (const row of rows) {
    if (typeof row[studentIdIdx] !== 'number' || typeof row[nameIdx] !== 'string' || typeof row[scoreIdx] !== 'number') {
      console.warn('Type error in row:', row);
      continue;
    }
    validated.push(row);
  }

  if (validated.length === 0) {
    console.warn('Cannot analyze: no valid rows');
    return;
  }

  console.log(`Loaded ${validated.length} exam scores`);

  // Step 2: Manually create histogram bins
  const bins = {
    underflow: { count: 0, label: 'underflow' },
    bin1: { count: 0, label: '[0, 10)' },
    bin2: { count: 0, label: '[10, 20)' },
    bin3: { count: 0, label: '[20, 30)' },
    bin4: { count: 0, label: '[30, 40)' },
    bin5: { count: 0, label: '[40, 50)' },
    bin6: { count: 0, label: '[50, 60)' },
    bin7: { count: 0, label: '[60, 70)' },
    bin8: { count: 0, label: '[70, 80)' },
    bin9: { count: 0, label: '[80, 90)' },
    bin10: { count: 0, label: '[90, 100)' },
    overflow: { count: 0, label: 'overflow' },
  };

  // Step 3: Count scores into bins manually
  const totalCount = validated.length;
  for (const row of validated) {
    const score = row[scoreIdx];
    if (score < 0) bins.underflow.count++;
    else if (score < 10) bins.bin1.count++;
    else if (score < 20) bins.bin2.count++;
    else if (score < 30) bins.bin3.count++;
    else if (score < 40) bins.bin4.count++;
    else if (score < 50) bins.bin5.count++;
    else if (score < 60) bins.bin6.count++;
    else if (score < 70) bins.bin7.count++;
    else if (score < 80) bins.bin8.count++;
    else if (score < 90) bins.bin9.count++;
    else if (score < 100) bins.bin10.count++;
    else bins.overflow.count++;
  }

  // Step 4: Calculate relative and cumulative frequencies manually
  let cumulativeCount = 0;
  const histogramData = [];
  const binOrder = ['underflow', 'bin1', 'bin2', 'bin3', 'bin4', 'bin5', 'bin6', 'bin7', 'bin8', 'bin9', 'bin10', 'overflow'];

  for (const binKey of binOrder) {
    const bin = bins[binKey];
    cumulativeCount += bin.count;
    const relativeFreq = (bin.count / totalCount).toFixed(4);
    const cumulativeFreq = (cumulativeCount / totalCount).toFixed(4);

    histogramData.push([bin.label, bin.count, relativeFreq, cumulativeFreq]);
  }

  console.log('Score distribution:', histogramData);

  // Step 5: Write histogram to sheet manually
  const histogramSheet = workbook.getSheetByName('Score Histogram')
    ?? workbook.insertSheet('Score Histogram');

  const histogramHeaders = ['bin', 'count', 'relative', 'cumulative_relative'];
  histogramSheet.getRange(1, 1, 1, histogramHeaders.length).setValues([histogramHeaders]);
  histogramSheet.getRange(2, 1, histogramData.length, histogramData[0].length).setValues(histogramData);

  // Step 6: Create a bar chart from the histogram data
  const chart = histogramSheet.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(histogramSheet.getDataRange())
    .setOption('title', 'Exam Score Distribution')
    .setOption('hAxis.title', 'Score Range')
    .setOption('vAxis.title', 'Number of Students')
    .setOption('legend.position', 'none')
    .setPosition(2, 5)
    .build();

  histogramSheet.insertChart(chart);

  // Step 7: Calculate pass rate manually (score ≥ 70)
  const passCount = bins.bin8.count + bins.bin9.count + bins.bin10.count;
  const passPercentage = (passCount / totalCount * 100).toFixed(1);

  console.log(`Pass rate (score ≥ 70): ${passPercentage}%`);

  // Step 8: Export statistics manually
  const statsSheet = workbook.getSheetByName('Exam Statistics')
    ?? workbook.insertSheet('Exam Statistics');

  // Calculate mean score manually
  let totalScore = 0;
  for (const row of validated) {
    totalScore += row[scoreIdx];
  }
  const meanScore = (totalScore / totalCount).toFixed(2);

  const statsData = [
    ['Metric', 'Value'],
    ['Total Exam Takers', totalCount],
    ['Passing Score (≥70)', passCount],
    ['Pass Rate (%)', passPercentage],
    ['Failing Count', totalCount - passCount],
    ['Mean Score', meanScore],
  ];

  statsSheet.getRange(1, 1, 1, 2).setValues([['Metric', 'Value']]);
  statsSheet.getRange(2, 1, statsData.length - 1, 2).setValues(statsData.slice(1));

  console.log('Score distribution analysis complete');
}

Without gaslamp, you need to:

• Manually define all bin ranges (12 bins × multiple conditions = complex conditional logic)
• Manually count scores into each bin with nested if-else statements
• Manually calculate relative frequency and cumulative frequency for each bin
• Track bin order manually to preserve insertion order
• Manually construct header row with bin labels and frequency columns
• Manually write histogram data to sheet row by row with explicit range dimensions
• Manually filter and sum counts to calculate pass rate
• Manually compute mean score with a separate reduction loop
• No automatic insights — all statistics must be hand-calculated

---

Key Techniques

• ForgeFrame for histograms: Use .h1() to create 1D histograms with automatic statistics (counts, frequencies, cumulative)
• Row-oriented output: Histogram results are BareFrame with one row per bin—ready for sheets and charts
• Direct sheet integration: .toSheet() writes histogram ready for charting (no manual cell filling)
• Google Sheets Charts API: Use newChart() with histogram data for visual distribution analysis
• Post-histogram filtering: Standard DataFrame operations work on histogram results
• Statistical aggregation: Use .reduce() to compute derived metrics (pass rate, percentiles) from histogram data
• Underflow/overflow tracking: Automatic handling of out-of-range values in dedicated bins

See ForgeFrame Guide for more histogram patterns, 2D histograms, and statistical analysis techniques.