Excel Formulas for Aggregating Incomplete Records with Last Known Values

Consolidating sparse datasets with missing time-series entries is a persistent hurdle for financial analysts. When tracking capital disbursements, teams often consolidate fragmented reports from standard funding sources, such as institutional grants, venture equity, and debt facilities. Having a unified view grants stakeholders immediate clarity on real-time runway by carrying forward the last known transaction values. However, this method carries the educational stipulation that data must be chronologically sorted to prevent calculation errors. Using XLOOKUP with exact-match-next-smaller-item parameters serves as a robust tool to forward-fill these gaps. Below, we outline the step-by-step formulas to automate this aggregation.

In data analytics, financial modeling, and operational reporting, we frequently encounter "sparse" or "incomplete" datasets. These datasets often record values only when a change occurs, leaving intervening rows blank. For example, a stock inventory log might only record entries on days when stock is added or removed, or an employee database might only log salary changes when a promotion occurs.

When you need to aggregate these records-such as finding the total inventory value or average salary across a specific timeframe-standard aggregation functions like SUM, AVERAGE, or SUMIFS yield incorrect results. They treat blank cells as zero or ignore them entirely, rather than carrying forward the Last Known Value (LKV) (also known as Last Observation Carried Forward, or LOCF).

This comprehensive guide explores robust Excel formulas to fill in these gaps dynamically and aggregate incomplete records using both modern dynamic array formulas and classic backward-compatible methods.

The Problem Illustrated

Consider the following raw dataset representing daily inventory levels for a retail item. New values are only recorded when inventory changes:

If you attempt to calculate the average daily inventory for this 5-day period using only the raw values, Excel calculates (50 + 35) / 2 = 42.5 (or 17 if it counts the blanks as zero). However, the true average inventory based on the actual daily state is (50 + 50 + 50 + 35 + 35) / 5 = 44. To get the correct aggregate, we must programmatically reconstruct the timeline with last known values.

Method 1: The Modern Excel Way (SCAN & LAMBDA)

For users on Office 365 or Excel 2021 and later, the introduction of lambda helper functions makes filling in incomplete records incredibly elegant. The SCAN function is designed specifically to loop through an array, apply a calculation to each element, and maintain an accumulator value across the operations.

The Formula

Assuming your raw values are in the range B2:B20, you can generate a filled array in an adjacent column using the following dynamic array formula:

=SCAN(0, B2:B20, LAMBDA(prev, curr, IF(curr="", prev, curr)))

How It Works

• 0 (Initial Value): This is the starting value of our accumulator (prev). If your dataset can start with a blank that needs to default to something else, you can adjust this initial value.
• B2:B20 (Array): The range of cells that Excel will scan row by row.
• LAMBDA(prev, curr, ...): A custom inline function. prev holds the accumulated value from the previous row, and curr represents the value of the current row Excel is evaluating.
• IF(curr="", prev, curr): The core logic. If the current row is blank, the formula carries forward the previous row's value (prev). If the current row contains a value, it accepts that value and updates the accumulator for the next row.

Direct Aggregation Without Helper Columns

If you don't want to output the filled array to your worksheet but instead want to aggregate it directly, you can wrap the SCAN function inside an aggregation function. For example, to calculate the correct average daily stock directly:

=AVERAGE(SCAN(0, B2:B20, LAMBDA(prev, curr, IF(curr="", prev, curr))))

Method 2: Classic Excel Approach (For Legacy Excel Versions)

If you are working in an environment that uses older versions of Excel (Excel 2019 or earlier), you won't have access to SCAN or LAMBDA. In this scenario, we rely on a helper column using traditional logical formulas or lookup techniques.

The Helper Column Formula

In your helper column (let's assume Column C, starting at row 2), enter the following formula and drag it down:

=IF(ISBLANK(B2), C1, B2)

This formula checks if the current raw cell (B2) is blank. If it is, it returns the value of the cell directly above it in the helper column (C1). If it is not blank, it pulls the new value from B2. This simple recursive reference effectively cascades the last known value down the entire column.

Aggregating the Results

Once your helper column is populated, standard aggregation formulas can be run directly on Column C:

=SUM(C2:C20)
=AVERAGE(C2:C20)

Method 3: Multi-Entity Last Known Value Aggregation

In real-world data, records are rarely isolated to a single item. More commonly, you will have multiple entities (e.g., different store locations or product names) mixed within the same sheet. You cannot simply carry forward the value from the row above if the entity has changed.

Consider this dataset where we need to track stock levels across different products:

The Solution: Grouped Last Known Value

To dynamically find the last known value matching a specific product, we can construct an array lookup that finds the most recent non-blank entry for that product. Assuming your columns are Date (Col A), Product (Col B), and Recorded Stock (Col C), enter this formula in row 2 of your helper column:

=IF(ISNUMBER(C2), C2, LOOKUP(2, 1/((B$2:B2=B2)*(C$2:C2<>"")), C$2:C2))

Breaking Down the LOOKUP Mechanism

• B$2:B2=B2: This creates an array of TRUE/FALSE values showing which rows up to the current row match the current product.
• C$2:C2<>"": This creates an array of TRUE/FALSE values showing which cells up to the current row contain non-blank values.
• 1/(...): By multiplying the two boolean arrays together, we get 1 for matching product rows that have data, and 0 for rows that do not. Dividing 1 by this array yields an array of 1s and #DIV/0! errors.
• LOOKUP(2, ...): The LOOKUP function ignores error values. By searching for the lookup value of 2 in an array containing only 1s and errors, Excel will fail to find 2 and default to matching the last numerical value (the last 1) in the array. This corresponds perfectly to the last known non-blank record for that specific product.

Advanced Aggregate Summing: SUMPRODUCT and LKV

If your goal is to find the total aggregate value at a specific point in time without physically filling the blanks in your sheet, you can combine the MAXIFS and INDEX/MATCH functions in an array format inside a SUMPRODUCT.

For example, to find the aggregate stock of all products on a specific target date (e.g., "2023-10-03"), you can run a query that searches for the closest date on or before the target date for each unique product, retrieves those values, and sums them up. While highly functional, this setup is resource-intensive on large datasets. If performance is a factor, generating filled tables using Power Query or the SCAN formula is highly recommended.

Performance and Best Practices

• Avoid Volatile Formulas: Traditional lookup arrays like LOOKUP(2, 1/...) can slow down workbook recalculations if applied across tens of thousands of rows. If performance drops, consider sorting your data chronologically and utilizing the simple helper column formula (=IF(ISBLANK(B2), C1, B2)), which calculates almost instantaneously.
• Leverage Power Query: If your dataset is massive, using Excel's built-in Power Query engine is often the best approach. You can load your table into Power Query, select your sparse columns, right-click, and select Fill -> Down. This performs the last known value operation during the data load phase, keeping your front-end Excel workbook incredibly lightweight and fast.

Conclusion

Aggregating incomplete records with carried-forward values is a common data cleanup step. With modern Excel, the SCAN function provides a neat, single-cell dynamic array solution that aggregates values on the fly. For legacy spreadsheets, structured helper columns using self-referencing logical formulas or LOOKUP arrays ensure your formulas remain backward-compatible without sacrificing accuracy. Choose the method that best aligns with your team's Excel version and the scale of your data.