Formula Errors

Fix an Excel AVERAGE Formula Returning -100% for Zero Values

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 872 views

Question details

The user needs an Excel percentage formula to return 0% instead of -100% when both comparison ranges contain zeros.

Fix an Excel AVERAGE Formula Returning -100% for Zero Values
Product
Microsoft Excel
Device & OS
not provided
Scenario
The formula compares an average with the average of the two largest values in another range.
Observed behavior
IFERROR replaces a division-by-zero error with 0 before the formula subtracts 1, producing -100%.
Before you start

Check that the formula references the intended ranges and that the denominator can round to zero. The example uses a semicolon in the array constant {1;2}; formula separators may differ by locale.

Solution 1Recommended

Include the Subtraction Inside Excel IFERROR

Use this formula when a calculation error, including division by zero, should produce 0%.

Moving -1 inside IFERROR ensures that the subtraction is part of the calculation being checked. If that calculation produces an error, IFERROR returns 0 instead.

1
Select the formula cell

In Excel, select the cell displaying -100% and click the formula bar.

2
Replace the formula

Enter =IFERROR(ROUND(AVERAGE($F7494:$F7495),2)/ROUND(AVERAGE(LARGE($F7490:$F7493,{1;2})),2)-1,0), adjusting the ranges if needed, and press Enter.

3
Check the result

With both ranges containing zeros, confirm that the cell returns 0. On the Home tab, select Percent Style if you want to display the result as 0%.

Include the Subtraction Inside Excel IFERROR
Other errors are also suppressed: IFERROR returns 0 for any error in the enclosed calculation, not just division by zero. A valid calculation can still return -100% when the numerator is zero and the denominator is nonzero.
Calculate percentages in WPS Spreadsheets

Fix the Same Percentage Formula in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and place the subtraction inside IFERROR so calculation errors return 0 instead of -100%.

  1. 1. Open the workbook: In WPS Spreadsheets, open the workbook containing the percentage formula.
  2. 2. Select the formula: Click the cell showing -100%, then click the formula bar.
  3. 3. Update the calculation: Enter =IFERROR(ROUND(AVERAGE($F7494:$F7495),2)/ROUND(AVERAGE(LARGE($F7490:$F7493,{1;2})),2)-1,0), changing the ranges if needed, and press Enter.
  4. 4. Display the percentage: With the formula cell selected, choose Percent Style on the Home tab and check that zero inputs display 0%.
Handle division errors in an AVERAGE-based percentage formulaOpen and edit Microsoft Excel XLSX filesFormat calculation results as percentagesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula show -100% when both ranges contain zeros?

If IFERROR returns 0 for a division-by-zero error and -1 is outside IFERROR, Excel subtracts 1 from that 0. Formatted as a percentage, the result is -100%.

Can the corrected formula still return -100%?

Yes. If the numerator is zero and the denominator is nonzero, the division succeeds and 0 minus 1 is a valid -100% result.

What if the denominator is not exactly zero?

The formula rounds the denominator to two decimal places before dividing. A small nonzero denominator can round to zero and trigger IFERROR.

Does IFERROR hide errors other than division by zero?

Yes. IFERROR returns the specified fallback for any error in the enclosed calculation. Check the referenced ranges and evaluate the calculation separately if an unexpected 0% appears.