Using SUMPRODUCT to Calculate Weighted Average in Real Estate

In my experience, using the SUMPRODUCT function in Excel to calculate weighted average is one of the most oft-used Excel techniques in real estate financial modeling. I learned this technique on day one of my first real estate internship and I continue to use it at least once a week to this day. So in this blog post, I’ll show you how to use this in your real estate financial modeling.

Note: If you’re an Accelerator member, Michael touches on this topic as it relates to course 1 in a recent forum Q&A. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.

The Math Behind Weighted Average

To fully understand this concept, it’s first necessary to think back to your 7th grade math class when Mrs. Cauliflower went over weighted average.

To teach weighted average, she first taught you to calculate the arithmetic mean (i.e. basic averaging formula). Or in other words, to calculate the average of a string of values you find the sum of that string of values, and then divide the total by the number of values in the string:

Average = (a₁ + a₂ + a₃ + … + a_n) ÷ n

or

5.8 = (5 + 7 + 4 + 3 + 10) ÷ 5

However, in some cases it’s necessary to assign greater or lesser weight to each value in the string. Thus, to allow for varying weight between each value in the string, we use the weighted average formula:

Weighted Average = (a₁ * v₁ + a₂ * v₂ + a₃ * v₃ + … + a_n * v_n) ÷ (v₁ + v₂ + v₃ + … + v_n)

or

5.57 = (5 * 1 + 7 * 2 + 4 * 1 + 3 * 2 + 10 * 1) ÷ (1 + 2 + 1 + 2 + 1)

To hammer this home, here’s my favorite weighted average explainer video on YouTube:

Weighted Average in Real Estate Financial Analysis

So how does Mrs. Cauliflower’s class on weighted averages or some random Youtuber’s explainer video on the subject relate to real estate financial modeling? Well, this logic is used frequently in real estate when averaging weighted values. As I mentioned above, I use it at least weekly and you will too.

I most commonly use the weighted average formula when averaging a set of sale or lease comps. Each comp will be weighted differently depending on, for instance, the number of units the comp has.

I also regularly use this concept when performing rent roll analysis or when calculating variable vs. fixed cash flow. Sometimes it even becomes necessary to write conditional weighted average logic in Excel in order to only weight and average specific values in an array.

And so knowing how to perform this calculation in Excel quickly and accurately is essential to being fully proficient modeling real estate in Excel.

Using SUMPRODUCT in Excel to Calculate Weighted Average

So how do you calculate weighted average in Excel? You really have two options.

The first option is to do the math the long way, as outlined above. Or in other words, write a formula that multiplies each value by its respective weight, add up the total, and then divide that total by the sum of the weights.

The problem with doing the weighted average calculation this way is that oftentimes, you’re dealing with a string of values many cells long. Imagine writing a formula to calculate the weighted average of a unit mix table with 50 unit types! That would involve writing a formula with 50 weights and 50 values – it would take several minutes to write!

So instead, there’s a much faster way: using Excel’s SUMPRODUCT() function.

If you’re unfamiliar with SUMPRODUCT in Excel, it essentially performs this portion (a₁ * v₁ + a₂ * v₂ + a₃ * v₃ + … + a_n * v_n) of your weighted average calculation instantly. Or in other words, it calculates the sum product of two (or more) arrays. Completing the weighted average calculation then is as simple as dividing that SUMPRODUCT() result by the SUM() of the weighted array.

Let me show you what I mean using a real-to-life real estate example and the following Excel logic:

Weighted Average = SUMPRODUCT(Component Array, Weight Array)/SUM(Weight Array)

• Click here to download the file used in the tutorial

And that’s using SUMPRODUCT in Excel to calculate weighted average when modeling real estate. If you have any questions, please let me know!