Multiply each value by its weight, add the products, divide by the sum of the weights. Two functions, one line, and three ways to get it wrong.
A weighted average in Excel is =SUMPRODUCT(values,weights)/SUM(weights). SUMPRODUCT multiplies each value by its weight and adds the products; the division by the total weight turns the sum into an average. On the rent roll of this example, the weighted average lease term of the portfolio is 3.72 years. A plain average of the same leases gives 3.05 years, and nothing in the cell says which one you are looking at.
The case comes from the AI in Excel for Analysts course by Julian R. Sterling, author of The Real Estate Asset Manager.Get the DRM-free PDF, $19.99 →
Alder House, in the fictional Brackenridge portfolio, has five units. Four are let; one is vacant.
| Unit | Tenant | Rent (EUR k) | Term (years) | Rent x term |
|---|---|---|---|---|
| A01 | Northgate Legal | 372.0 | 2.50 | 930.0 |
| A02 | Pellam Software | 255.0 | 0.75 | 191.2 |
| A03 | Vacant | 0.0 | 0.00 | 0.0 |
| A04 | Orrin Consulting | 304.0 | 4.25 | 1,292.0 |
| A05 | Corner Cafe | 60.0 | 2.00 | 120.0 |
| Total | 991.0 | 2,533.2 |
2,533.2 divided by 991.0 gives 2.56 years. The vacant unit has no rent, so it carries no weight. In one cell, with the rent in G5:G9 and the term in J5:J9: =SUMPRODUCT(G5:G9,J5:J9)/SUM(G5:G9).
When the table holds several properties, add a condition inside SUMPRODUCT and use SUMIFS for the weights:
=SUMPRODUCT((Data!$C$5:$C$24=D$4)*Data!$G$5:$G$24*Data!$J$5:$J$24)/SUMIFS(Data!$G$5:$G$24,Data!$C$5:$C$24,D$4)
The test Data!$C$5:$C$24=D$4 returns 1 on the rows of the property named in D4 and 0 elsewhere. The three ranges must have the same height, or the formula returns an error.
| Method | Alder House | Birch Court | Cedar Yard | Dunmore Park | Portfolio |
|---|---|---|---|---|---|
| Weighted by rent (WALT) | 2.56 | 2.87 | 4.70 | 4.80 | 3.72 |
| Plain average of the let units | 2.38 | 2.00 | 3.00 | 4.25 | 3.05 |
| Weighted by area | 2.53 | 2.86 | 4.64 | 4.80 | 4.19 |
The three lines rarely agree. At Cedar Yard, the plain average is 3.00 years against 4.70 weighted by rent. For the portfolio, the same leases give 3.72, 3.05 or 4.19 years. Each figure is a correct answer to a different question: state the weight in the label of the line.
The portfolio figure, 3.72 years, weights every lease by its rent. Averaging the four property figures gives 3.73 years. The two are close here by coincidence: an average of subtotals gives the smallest property the same weight as the largest, and the gap opens as soon as the properties differ in size and in lease length. Compute a total from the rows, never from the subtotals.
Say what the weight is. "Average lease term by property" returns AVERAGEIFS. "Average remaining term weighted by passing rent, rent in column G, term in column J, rows 5 to 24, property name in D4, copied to the right" returns the formula above. Then test it: on four invented leases where X has 100 of rent for 2 years and 200 for 5 years, the answer must be 4.00, not 3.50.
Every figure is computed by script from the course's running case, the Brackenridge portfolio, a fictional rent roll of 20 units, and checked on 19 November 2026. Function availability depends on your version of Excel. For education, not investment advice.
26 short video lessons on using an AI assistant in Excel without being wrong: the five-part brief, SUMIFS, SUMPRODUCT, INDEX and MATCH, a mini sample with a known answer, checks that tie and the audit of a received model. You build a checked summary from a rent roll, with step files, exercises, a prompt kit and a certificate exam ($149).
SUMPRODUCT of the values and the weights, divided by the SUM of the weights: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10). Excel has no single weighted average function.
Put the condition inside SUMPRODUCT as a test that returns 1 or 0, multiplied by the values and the weights, and divide by a SUMIFS of the weights on the same condition.
Because averaging subtotals gives each group the same weight. A weighted total must be computed from the underlying rows, with each row carrying its own weight.
This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
See the course →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Prefer a PDF? The same book is sold as a DRM-free PDF edition, searchable and printable. Get the PDF edition, $19.99