run2-excel-sumproduct

name	run2_excel-sumproduct
description	Calculate GDP-weighted mean of net exports as % of GDP using SUMPRODUCT in Excel, including all supporting statistics (min, max, median, percentiles).

Excel SUMPRODUCT: GDP-Weighted Mean and Statistics

Net Exports % of GDP (Step 2 Formula)

=ROUND((H12 - H19) / H26 * 100, 1)

• H12 = Exports; H19 = Imports; H26 = GDP
• Multiply by 100 to express as percentage points (e.g., 19.7 not 0.197)
• ROUND to 1 decimal place per task requirements

Row Mapping in Task Sheet

Step 2 Row	Country	Exports Row	Imports Row	GDP Row
35	UAE	12	19	26
36	Bahrain	13	20	27
37	Kuwait	14	21	28
38	Qatar	15	22	29
39	Oman	16	23	30
40	Saudi Arabia	17	24	31

Statistics Formulas (H42:L47)

All statistics use the net exports % range for that year column:

Row 42 (min):     =ROUND(MIN(H35:H40), 1)
Row 43 (max):     =ROUND(MAX(H35:H40), 1)
Row 44 (median):  =ROUND(MEDIAN(H35:H40), 1)
Row 45 (mean):    =ROUND(AVERAGE(H35:H40), 1)
Row 46 (P25):     =ROUND(PERCENTILE(H35:H40, 0.25), 1)
Row 47 (P75):     =ROUND(PERCENTILE(H35:H40, 0.75), 1)

GDP-Weighted Mean (Step 3)

=ROUND(SUMPRODUCT(H35:H40, H26:H31) / SUM(H26:H31), 1)

Why This Works

• H35:H40 = net exports % (already × 100)
• H26:H31 = GDP weights
• SUMPRODUCT(pct, GDP) / SUM(GDP) = weighted average
• Since pct is already ×100, result is in percentage points

Mathematical Equivalence

Weighted Mean = Σ(pct_i × GDP_i) / Σ(GDP_i)
              = Σ((Exports_i - Imports_i) / GDP_i × 100 × GDP_i) / Σ(GDP_i)
              = Σ(Exports_i - Imports_i) × 100 / Σ(GDP_i)

Sample Values (2019)

• UAE: 19.7%, weight 418.0B
• Bahrain: 11.3%, weight 38.7B
• Kuwait: 12.6%, weight 138.7B
• Qatar: 14.3%, weight 176.4B
• Oman: 12.5%, weight 88.1B
• Saudi Arabia: 8.0%, weight 838.6B
• Weighted Mean ≈ 12.2%

openpyxl Implementation

cols = ['H', 'I', 'J', 'K', 'L']

# Net exports % per country
for i in range(6):
    row = 35 + i
    for col in cols:
        task[f'{col}{row}'] = f'=ROUND(({col}{12+i}-{col}{19+i})/{col}{26+i}*100,1)'

# Statistics
for col in cols:
    task[f'{col}42'] = f'=ROUND(MIN({col}35:{col}40),1)'
    task[f'{col}43'] = f'=ROUND(MAX({col}35:{col}40),1)'
    task[f'{col}44'] = f'=ROUND(MEDIAN({col}35:{col}40),1)'
    task[f'{col}45'] = f'=ROUND(AVERAGE({col}35:{col}40),1)'
    task[f'{col}46'] = f'=ROUND(PERCENTILE({col}35:{col}40,0.25),1)'
    task[f'{col}47'] = f'=ROUND(PERCENTILE(35:40,0.75),1)'


 col  cols:
    task[] =