Learn Weighted Calculations and Conditional Logic | Dynamic Financial and Time-Based Modeling

Swipe to show menu

SUMIFS works well for standard conditional totals, but some analytical calculations require weighting and embedded logic. In this chapter, extend the aggregation system using SUMPRODUCT.

SUMPRODUCT Structure

=SUMPRODUCT(array1, [array2], [array3], ...)

array1: first calculation array;
[array2]: optional second array;
[array3]: optional additional arrays.

SUMPRODUCT multiplies corresponding values and then sums the results.

Logical conditions inside the formula are automatically converted into:

TRUE = 1;
FALSE = 0.

Weighted Average Structure

=SUMPRODUCT(values * weights) / SUM(weights)

values: evaluated metric;
weights: importance factor.

Weighted averages give larger transactions greater influence on the final result.

Step 1 Calculate Average Revenue per Order

Type:

=SUMPRODUCT(Sales_Data[Revenue]) / COUNTA(Sales_Data[Revenue])

SUMPRODUCT(...): total Revenue;
COUNTA(...): number of Revenue rows.

The formula returns the average Revenue per order.

Step 2 Build a Conditional Average

Type:

=SUMPRODUCT((Sales_Data[Region]="North")*Sales_Data[Revenue]) / SUMPRODUCT((Sales_Data[Region]="North")*1)

(Sales_Data[Region]="North"): logical condition;
*Sales_Data[Revenue]: filtered Revenue values;
SUMPRODUCT(...*1): conditional row count.

The formula calculates the average Revenue for the North region only.

Step 3 Calculate Weighted Average Discount Rate

Type:

=SUMPRODUCT(Sales_Data[Discount_Rate]*Sales_Data[Revenue]) / SUM(Sales_Data[Revenue])

Sales_Data[Discount_Rate]: discount percentages;
Sales_Data[Revenue]: weighting factor.

Higher Revenue transactions now influence the average discount more heavily.

Step 4 Build High-Tier Revenue Analysis

Inside the Summary table, type:

=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],A2,Sales_Data[Performance tier],"High")

Sales_Data[Region],A2: region filter;
Sales_Data[Performance tier],"High": performance filter.

The formula returns Revenue generated only by high-performing transactions.

Step 5 Build High-Tier Order Count

Type:

=COUNTIFS(Sales_Data[Region],A2,Sales_Data[Performance tier],"High")

The formula counts all high-tier orders for the selected region.

Step 6 Calculate High-Tier Revenue Percentage

Type:

=E2/C2

E2: high-tier Revenue;
C2: total Revenue.

The formula measures the share of Revenue generated by high-performing transactions.

Step 7 Format the Results

Apply:

Currency formatting to Revenue metrics;
Percentage formatting to ratios and discount calculations.

Step 8 Test Dynamic Behavior

Add additional rows into Sales_Data.

Confirm that:

Average Revenue recalculates automatically;
Weighted discount calculations update automatically;
High-tier metrics update automatically.

Everything was clear?

Thanks for your feedback!

Section 4. Chapter 5

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 4. Chapter 5

Weighted Calculations and Conditional Logic

SUMPRODUCT Structure

Weighted Average Structure

1. Why is `SUMPRODUCT` useful for weighted calculations?

2. What is a key limitation of `SUMIFS` compared to `SUMPRODUCT`?

3. In a weighted average formula using `SUMPRODUCT`, what does the denominator represent?

Weighted Calculations and Conditional Logic

SUMPRODUCT Structure

Weighted Average Structure

1. Why is SUMPRODUCT useful for weighted calculations?

2. What is a key limitation of SUMIFS compared to SUMPRODUCT?

3. In a weighted average formula using SUMPRODUCT, what does the denominator represent?

1. Why is `SUMPRODUCT` useful for weighted calculations?

2. What is a key limitation of `SUMIFS` compared to `SUMPRODUCT`?

3. In a weighted average formula using `SUMPRODUCT`, what does the denominator represent?