excel-weighted-mean
Calculating GDP-weighted means and other weighted statistics in Excel using SUMPRODUCT, including net exports as percent of GDP.
- License 许可
- MIT
- 最近更新
- 2026-10-02
- Stars
- ★ 93
- Forks
- 8
01 前言 Front Matter
- name
- excel-weighted-mean
- description
- Calculating GDP-weighted means and other weighted statistics in Excel using SUMPRODUCT, including net exports as percent of GDP.
03 SKILL.md 正文
Excel Weighted Mean with SUMPRODUCT
Overview
A weighted mean assigns different weights to each observation. In economic analysis, GDP-weighted averages give more influence to larger economies.
Formula
=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)
Example: GDP-Weighted Net Exports % GDP
=ROUND(SUMPRODUCT(H35:H40, H26:H31) / SUM(H26:H31), 1)
Where:
H35:H40— net exports as % of GDP for each country (already in percentage form, e.g., 12.3 means 12.3%)H26:H31— GDP values in billions (used as weights)- Result is automatically in the same scale as the input values (12.3 form, not 0.123)
Scale Consistency
If values in H35:H40 are stored as 12.3 (percent form), the weighted mean formula returns the result in the same 12.3 form — no additional multiplication by 100 is needed.
Net Exports as Percent of GDP
=ROUND((Exports - Imports) / GDP * 100, 1)
Example where exports are in row 12, imports in row 19, GDP in row 26:
=ROUND((H12 - H19) / H26 * 100, 1)
Related Statistical Functions
All rounded to 1 decimal for consistency:
=ROUND(MIN(H35:H40), 1) ' Minimum
=ROUND(MAX(H35:H40), 1) ' Maximum
=ROUND(MEDIAN(H35:H40), 1) ' Median
=ROUND(AVERAGE(H35:H40), 1) ' Simple mean
=ROUND(PERCENTILE(H35:H40, 0.25), 1) ' 25th percentile
=ROUND(PERCENTILE(H35:H40, 0.75), 1) ' 75th percentile
=ROUND(SUMPRODUCT(H35:H40, H26:H31) / SUM(H26:H31), 1) ' GDP-weighted mean
SUMPRODUCT for Weighted Mean vs Simple Mean
| Formula | Description |
|---|---|
=AVERAGE(H35:H40) |
Simple mean (equal weights) |
=SUMPRODUCT(H35:H40, H26:H31)/SUM(H26:H31) |
GDP-weighted mean |
The weighted mean will differ from the simple mean because larger economies pull the average toward their values.
Setting in Python (openpyxl)
from openpyxl import load_workbook
wb = load_workbook('file.xlsx')
ws = wb['Task']
# Weighted mean formula
ws['H50'] = '=ROUND(SUMPRODUCT(H35:H40,H26:H31)/SUM(H26:H31),1)'
# Statistics
ws['H42'] = '=ROUND(MIN(H35:H40),1)'
ws['H43'] = '=ROUND(MAX(H35:H40),1)'
ws['H44'] = '=ROUND(MEDIAN(H35:H40),1)'
ws['H45'] = '=ROUND(AVERAGE(H35:H40),1)'
ws['H46'] = '=ROUND(PERCENTILE(H35:H40,0.25),1)'
ws['H47'] = '=ROUND(PERCENTILE(H35:H40,0.75),1)'
wb.save('file.xlsx')
04 同仓库更多 Skills
object_counter
Count occurrences of an object in the image using computer vision algorithm.
travel-planning-workflow
Invoke this skill when performing travel planning. It defines a structured workflow for generating itineraries. Following this workflow is essential to ensure the final plan satisfies user requirements.
bundled-dataset-search-tool
invoke this skill when you need to perform database search for travel planning. This skill provides some useful pre-packaged tools to look up accommodations, attractions, cities, driving distance, flights, and restaurants from the bundled dataset.
glm-output
Output-processing guidance for GLM tasks. Especially useful after glm-basics and glm-calibration when you need verifier-matching metrics from output.nc and a final /root/metrics.json.
glm-calibration
Calibration guidance for GLM tasks. Often most effective after glm-basics has clarified the setup; glm-output is the companion skill for exact final metric computation.
glm-basics
A good starting point for GLM calibration tasks. Use to inspect glm3.nml, confirm how GLM runs, and identify the relevant files before moving on to calibration and output evaluation.