Calculation Issues

How to Calculate an Expanding Daily Average NAV by Class in Excel

Jason Miller Oct 20, 2026 868 views

Question details

Calculate a cumulative, expanding daily average of Net Asset Value (NAV) separately for specific asset classes in Excel.

Calculate an Expanding Daily Average NAV by Class in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking daily financial metrics where the average NAV needs to be calculated progressively (e.g., 1-day average, then 2-day average) per category as new data rows are added.
Observed behavior: The goal is to produce a dynamic row-by-row average that groups values by class and calculates all occurrences up to the current row without including future rows.

Before you start

Ensure your dataset is sorted chronologically by date in ascending order so that the expanding average logic correctly represents time progression.

Solution 1Recommended

Use the AVERAGEIF Function with an Expanding Range

Combine the AVERAGEIF function with a mixed absolute and relative reference to create a cumulative average that groups by class.

An expanding range anchors the starting cell using absolute references (like $B$2) but leaves the ending cell as a relative reference (like B2). As you drag the formula down, the range expands to include the current row, calculating a progressive average.

Select the target cell

Click on the cell where you want the first expanding average to appear, such as cell D2.

Enter the AVERAGEIF formula

Type the formula =AVERAGEIF($B$2:B2,B2,$C$2:C2) into the formula bar. In this setup, Column B contains your classes and Column C contains the NAV values.

Apply the formula to the column

Press Enter, then click the small square at the bottom-right corner of cell D2 and drag it down to fill the formula for the rest of your data.

Use the AVERAGEIF Function with an Expanding Range

Formula Breakdown: The first parameter ($B$2:B2) evaluates the expanding range of classes. The second (B2) checks against the current row's class. The third ($C$2:C2) averages the corresponding NAV values.

Calculate Expanding Averages Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced statistical functions like AVERAGEIF and expanding ranges, making it an excellent free solution for tracking complex financial metrics such as NAV averages.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your daily NAV values and class categories.
2. Input the AVERAGEIF formula: Select the first empty cell in your average column (e.g., D2) and enter =AVERAGEIF($B$2:B2,B2,$C$2:C2).
3. Calculate the first row: Press the Enter key to generate the initial average for the first data entry.
4. Fill the formula down: Double-click the fill handle in the bottom-right corner of the selected cell to automatically apply the expanding average to all subsequent rows.

100% compatible with Microsoft Excel formulas and formattingLightweight architecture for handling large financial datasets quicklyBuilt-in data analysis and sorting tools to streamline reportingCompletely free to download and use for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my expanding average returning an error when I copy it down?

This usually happens if you forgot to lock the starting reference in your formula. Ensure the starting cell has dollar signs (e.g., $B$2), while the ending cell does not (e.g., B2).

Can I calculate an expanding average with multiple conditions?

Yes, you can use the AVERAGEIFS function instead. The logic for the expanding range remains the same. For example: =AVERAGEIFS($C$2:C2, $B$2:B2, B2, $A$2:A2, A2) to filter by both Class and Date.

Does my data need to be sorted by class for AVERAGEIF to work?

No, AVERAGEIF will correctly identify and calculate averages for the specific class regardless of order. However, the data must be sorted chronologically by date for the expanding range to accurately reflect a daily progression.

How do I ignore zero values in my expanding average?

If you need to exclude zero values from the NAV calculation, switch to the AVERAGEIFS function and add a condition to exclude zeros: =AVERAGEIFS($C$2:C2, $B$2:B2, B2, $C$2:C2, ">0").

Excel & Data Problems