Function Problems

Calculate an Excel Average Starting at the First Nonzero Value

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

Calculate an average and other statistics across each row, starting at the first nonzero value.

Calculate an Excel Average Starting at the First Nonzero Value
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains records across rows, with leading zeros that should be excluded from statistical calculations.
Observed behavior
The desired calculation excludes leading zeros but includes zeros that occur after the first nonzero value.
Before you start

Check that A2:X2 contains numeric values and adjust the range to match your data. Place each formula outside the source range, such as in Z2, to avoid a circular reference.

Solution 1Recommended

Use XLOOKUP to average from the first nonzero value

Use this formula in Microsoft 365 or Excel 2021 to create a dynamic range for each row.

1
Enter the average formula

Select Z2 and enter =IFERROR(AVERAGE(XLOOKUP(TRUE,A2:X2<>0,A2:X2):X2),"").

2
Apply it to other rows

Drag the fill handle from Z2 down alongside your records. The row references will adjust automatically.

3
Calculate another statistic

For population standard deviation, select AA2 and enter =IFERROR(STDEV.P(XLOOKUP(TRUE,A2:X2<>0,A2:X2):X2),"").

Use XLOOKUP to average from the first nonzero value
How the range works: The range starts at the first value that is not zero and ends at X2, so later zeros are included. IFERROR returns a blank if no nonzero value is found or another calculation error occurs.
Calculate in WPS Spreadsheets

Average from the first nonzero value in WPS Spreadsheets

Use an INDEX and MATCH formula in WPS Spreadsheets to exclude leading zeros while retaining later zeros in the average.

  1. 1. Open your workbook: Open WPS Spreadsheets and use File > Open to select your workbook.
  2. 2. Select an output cell: Select Z2, or another cell outside the data range A2:X2.
  3. 3. Enter the formula: Enter =IFERROR(AVERAGE(INDEX(A2:X2,1,MATCH(TRUE,A2:X2<>0,0)):X2),""). If your version requires array entry, confirm with Ctrl+Shift+Enter.
  4. 4. Apply it to other records: Drag the fill handle down from the formula cell to calculate the remaining rows.
Calculate averages from the first nonzero value in each row.Open and edit Microsoft Excel XLS and XLSX files.Use familiar spreadsheet formulas and a familiar interface.Get started with WPS Office for free.
microsoft office alternative - wps office

Frequently Asked Questions

Are zeros after the first nonzero value included in the average?

Yes. The formula averages the entire range from the first nonzero value through X2, including any later zeros.

What happens if a row contains only zeros?

The formulas return a blank because MATCH or XLOOKUP cannot find a nonzero value and IFERROR handles the resulting error.

Will a negative number count as the first nonzero value?

Yes. The condition A2:X2<>0 finds the first nonzero value, whether it is positive or negative.

Can I calculate standard deviation over the same range?

Yes. Replace AVERAGE with STDEV.P in the formula to calculate population standard deviation over the same dynamic range.

Why do I need Ctrl+Shift+Enter?

Some older Excel versions require array formulas to be confirmed with Ctrl+Shift+Enter. Use it after typing or editing the INDEX and MATCH formula if your version requires array entry.