Run2 excel statistics aggregates
[COLM'26] SkillLearnBench is the first benchmark for evaluating continual learning methods that automatically generate agent skills.
npx -y skills add cxcscmu/SkillLearnBench --skill run2_excel-statistics-aggregatesAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
What its author says it does
Copied from the file, not written here
Excel statistical functions for datasets including MIN, MAX, MEDIAN, AVERAGE, QUARTILE, and SUMPRODUCT
SKILL.md
3.8 KB, as published. Nobody here has run it
Excel Statistical and Aggregate Functions
Central Tendency Functions
AVERAGE (Arithmetic Mean)
Returns the arithmetic mean of a range.
=AVERAGE(H35:H40)
- Simple mean across all values
- Treats all values equally
- Returns single decimal value
MEDIAN
Returns the middle value in a sorted range.
=MEDIAN(H35:H40)
- Middle value when data is sorted
- For 6 values: average of 3rd and 4th values
- Robust to outliers
Extreme Values
MIN
Returns the minimum value in a range.
=MIN(H35:H40)
MAX
Returns the maximum value in a range.
=MAX(H35:H40)
Percentile Functions
QUARTILE (Excel 2007+) - Recommended
Returns quartile values for a dataset.
Syntax: =QUARTILE(range, quart)
Parameters:
- range: Data range to analyze (e.g., H35:H40)
- quart: Quartile number (0-4)
- 0: Minimum value
- 1: 25th percentile (Q1)
- 2: Median/50th percentile
- 3: 75th percentile (Q3)
- 4: Maximum value
Examples:
=QUARTILE(H35:H40, 1) # 25th percentile
=QUARTILE(H35:H40, 3) # 75th percentile
PERCENTILE (Alternative Syntax)
Returns percentile value with flexible precision.
Syntax: =PERCENTILE(range, k)
Parameters:
- range: Data range
- k: Percentile value (0 to 1)
- 0.25 for 25th percentile
- 0.5 for 50th percentile (median)
- 0.75 for 75th percentile
Examples:
=PERCENTILE(H35:H40, 0.25) # 25th percentile
=PERCENTILE(H35:H40, 0.75) # 75th percentile
Weighted Aggregates
SUMPRODUCT (Weighted Sum and Weighted Mean)
For weighted sum:
=SUMPRODUCT(values_range, weights_range)
Multiplies each value by its weight, then sums results.
For weighted mean:
=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)
Example - Weighted mean of GDP by country:
=SUMPRODUCT(H35:H40, H26:H31) / SUM(H26:H31)
Example - Aggregate net exports as % of GDP for region:
=(SUM(Exports) - SUM(Imports)) / SUM(GDP) * 100
Rounding Statistical Results
Always round when displaying statistics as percentages or to specific decimal places:
=ROUND(MIN(H35:H40), 1)
=ROUND(MAX(H35:H40), 1)
=ROUND(MEDIAN(H35:H40), 1)
=ROUND(AVERAGE(H35:H40), 1)
=ROUND(QUARTILE(H35:H40, 1), 1)
=ROUND(QUARTILE(H35:H40, 3), 1)
Statistical Function Selection Guide
| Need | Function | Example |
|---|---|---|
| Middle value | MEDIAN | =MEDIAN(range) |
| Average | AVERAGE | =AVERAGE(range) |
| Smallest | MIN | =MIN(range) |
| Largest | MAX | =MAX(range) |
| 25th percentile | QUARTILE(...,1) | =QUARTILE(range,1) |
| 75th percentile | QUARTILE(...,3) | =QUARTILE(range,3) |
| Weighted average | SUMPRODUCT | =SUMPRODUCT(vals,wts)/SUM(wts) |
| Aggregate %* | SUM ratio | =(SUM(E)-SUM(I))/SUM(G)*100 |
*For aggregate metrics like GCC-wide net exports %
Performance Considerations
- SUMPRODUCT vs. array formulas: SUMPRODUCT is simpler and more compatible across Excel versions
- Data range size: All functions handle large ranges efficiently (tested up to millions of rows)
- Empty cells: Most functions ignore empty cells; verify if zeros should be included
Common Patterns for Data Analysis
Compare Individual vs. Aggregate
Individual (per country): =ROUND(AVERAGE(H35:H40), 1)
Aggregate (for region): =ROUND((SUM(H12:H17)-SUM(H19:H24))/SUM(H26:H31)*100, 1)
Detect Outliers Using Quartiles
Q1: =QUARTILE(range, 1)
Q3: =QUARTILE(range, 3)
IQR: =Q3 - Q1
Outlier threshold: Values > Q3 + 1.5*IQR or < Q1 - 1.5*IQR
Multi-Dimensional Aggregation
=SUMPRODUCT(criteria_range=criteria_value, aggregate_range) / SUMPRODUCT(criteria_range=criteria_value, weight_range)