Function Problems

Round an Excel IF Result Up to a Multiple of 10

Steve KSteve K Nov 12, 2026 869 views

Question details

Create an IF formula that returns 0 when cell E5 is greater than 80; otherwise, round the value in E5 up to a multiple of 10.

Round an Excel IF Result Up to a Multiple of 10
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user needs to apply conditional logic and upward rounding to a value in cell E5.
Observed behavior
The desired result is 0 for values above 80 and the next applicable multiple of 10 for values of 80 or below.
Before you start

Confirm that E5 contains a numeric value and that your Excel version supports the CEILING.MATH function. Enter the formula in the result cell, then fill it down if you need the same logic for additional rows.

Solution 1Recommended

Use IF with CEILING.MATH to round E5 up to a multiple of 10

Use this formula when values greater than 80 must return 0 and all other positive values must be rounded upward to a multiple of 10.

CEILING.MATH rounds a number up to the specified significance. In this formula, 10 is the significance, so values such as 41 and 49 return 50, while 50 remains 50.

1
Select the result cell

Click the cell where you want the conditional rounded result to appear, such as F5.

2
Enter the formula

Type or paste =IF(E5>80,0,CEILING.MATH(E5,10)) and press Enter.

3
Copy the formula if needed

Drag the fill handle from the result cell down the column to apply the formula to other rows; Excel updates E5 to the matching row reference automatically.

Use IF with CEILING.MATH to round E5 up to a multiple of 10
Formula result: If E5 is 81, the result is 0. If E5 is 41, the result is 50. If E5 is already 50, the result remains 50.
Calculate with WPS Spreadsheets

Apply Conditional Rounding Formulas in WPS Spreadsheets

WPS Spreadsheets lets you combine IF and CEILING.MATH functions to return 0 above a threshold and round remaining values up to multiples of 10. Its familiar spreadsheet interface makes it easy to enter formulas and fill them through a column.

  1. 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file in WPS Office, then select the worksheet containing cell E5.
  2. 2. Select the destination cell: Click the cell where the calculated result should appear, such as F5.
  3. 3. Enter the conditional rounding formula: Type =IF(E5>80,0,CEILING.MATH(E5,10)) in the formula bar and press Enter.
  4. 4. Apply the formula to more rows: Select the formula cell and drag its fill handle downward to copy the formula to adjacent rows.
Use IF and CEILING.MATH to round values upward to multiples of 10.Fill formulas down a column with the familiar drag handle.Open and edit Microsoft Excel .xlsx and .xls files with format compatibility.Use a lightweight, free spreadsheet tool with familiar formula editing.
QA img-9

Frequently Asked Questions

What does =IF(E5>80,0,CEILING.MATH(E5,10)) do?

It checks whether E5 is greater than 80. If it is, the formula returns 0; otherwise, CEILING.MATH rounds E5 upward to a multiple of 10.

Will CEILING.MATH change a value that is already a multiple of 10?

No. With =CEILING.MATH(E5,10), a value such as 20, 50, or 80 remains unchanged because it is already an exact multiple of 10.

Can I use ROUNDUP instead of CEILING.MATH?

Yes, for positive values you can use =IF(E5>80,0,ROUNDUP(E5,-1)). The -1 argument rounds upward to the tens place.

How do I apply the formula to every row in an Excel column?

Enter the formula in the first result cell, then drag the small fill handle at the cell's bottom-right corner down the column. Excel adjusts E5 to E6, E7, and subsequent row references.

Why does my formula show an error instead of a rounded result?

Check that E5 contains a number rather than text and that the formula uses commas or semicolons according to your Excel regional settings. Also verify that parentheses are balanced.