Excel advanced formulas

Excel Advanced Formulas Guide

This skill provides correct syntax and usage for common advanced Excel formulas, specifically for two-way lookups, statistical functions, and weighted means.

Lookups with Two Criteria

When you need to look up a value based on two criteria (e.g., matching a row and a column), use INDEX/MATCH, VLOOKUP/MATCH, or HLOOKUP/MATCH.

VLOOKUP with MATCH (2D Lookup)

=VLOOKUP(row_lookup_value, table_array, MATCH(col_lookup_value, header_range, 0), FALSE) Where header_range starts from the same column as table_array and spans across.

INDEX / MATCH (2D Lookup)

=INDEX(data_range, MATCH(row_lookup_value, row_labels_range, 0), MATCH(col_lookup_value, col_labels_range, 0))

XLOOKUP (Nested for 2D Lookup)

=XLOOKUP(row_lookup_value, row_labels_range, XLOOKUP(col_lookup_value, col_labels_range, data_range))

Statistical Functions

Use these formulas for basic descriptive statistics over a range:

• Min: =MIN(range)
• Max: =MAX(range)
• Median: =MEDIAN(range)
• Simple Mean: =AVERAGE(range)
• 25th Percentile: =PERCENTILE.INC(range, 0.25)
• 75th Percentile: =PERCENTILE.INC(range, 0.75)

Weighted Mean using SUMPRODUCT

To calculate a weighted mean (e.g., a metric weighted by GDP): =SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

Note on Percentages: If requirements state percentages should be rounded to one decimal place as whole numbers (e.g., 12.3 instead of 0.123), multiply the final calculation by 100: =(SUMPRODUCT(values, weights) / SUM(weights)) * 100 Or for regular formulas: = (value / total) * 100