Function Problems

Round an Excel Formula to the Nearest Multiple of Four

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

Round a calculated Excel result to the nearest multiple of four and display a blank if the calculation produces an error.

Round an Excel Formula to the Nearest Multiple of Four
Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula excludes the highest and lowest values in D4:G4, averages the remaining values, and applies a percentage adjustment.
Observed behavior
The desired result is a multiple of four, or a blank cell when the underlying calculation cannot be completed.
Before you start

Check that D4:G4 contains more than two numeric values and that the sheet named % has the intended percentage in J5. For example, enter 10% rather than 10 for a 10% adjustment.

Solution 1Recommended

Use ROUND and IFERROR to round to a multiple of four

Use this approach for positive or negative results while displaying a blank if the calculation fails.

Dividing the result by four, rounding to a whole number, and multiplying by four returns the nearest multiple of four. IFERROR covers the entire calculation, including the rounding step.

1
Select the result cell

Click the Excel cell where you want the rounded result to appear.

2
Enter the formula

In the formula bar, enter =IFERROR(ROUND(((SUM(D4:G4)-MAX(D4:G4)-MIN(D4:G4))/(COUNT(D4:G4)-2)*(1+'%'!J5))/4,0)*4,"") and press Enter.

3
Check the result

Review the selected cell: it should show a multiple of four, or remain blank if the calculation returns an error.

Use ROUND and IFERROR to round to a multiple of four
Halfway values: A value exactly halfway between two multiples is rounded away from zero. For example, 6 becomes 8 and -6 becomes -8.
Round spreadsheet calculations

Round to Multiples of Four in WPS Spreadsheet

Use ROUND and IFERROR in WPS Spreadsheet to round the calculation to a multiple of four and leave the result blank if an error occurs.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Check the inputs: Inspect D4:G4 and cell J5 on the sheet named % to confirm the numbers and percentage adjustment.
  3. 3. Enter the formula: Select the result cell and enter =IFERROR(ROUND(((SUM(D4:G4)-MAX(D4:G4)-MIN(D4:G4))/(COUNT(D4:G4)-2)*(1+'%'!J5))/4,0)*4,"") in the formula bar, then press Enter.
  4. 4. Save the workbook: Check the result, then use File > Save to save your changes.
Round positive and negative results to the nearest multiple of fourDisplay a blank when the calculation returns an errorOpen and edit Microsoft Excel XLSX and XLS filesWork with formulas in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why should IFERROR wrap the entire formula?

Wrapping the full rounding formula in IFERROR catches errors from both the underlying calculation and the rounding operation. The second argument, "", displays a blank when an error occurs.

What happens if D4:G4 has fewer than three numbers?

The calculation cannot produce a valid average after removing the highest and lowest values. With IFERROR around the formula, the result cell displays a blank.

Can the formula round negative values?

Yes. The ROUND calculation divides by four, rounds, and multiplies by four for both positive and negative values. MROUND with a positive multiple of 4 returns an error for a negative number in Excel.

How do I enter the percentage adjustment?

On the sheet named %, enter the adjustment in J5 as a percentage, such as 10%. The formula multiplies the calculated average by 1 plus that value.