Round an Excel IF Result Up to a Multiple of 10
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.

- 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.
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.
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.
Click the cell where you want the conditional rounded result to appear, such as F5.
Type or paste =IF(E5>80,0,CEILING.MATH(E5,10)) and press Enter.
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 ROUNDUP as an alternative for positive whole-number tens
Use this shorter alternative when you only need to round positive values upward to the tens place.
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. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file in WPS Office, then select the worksheet containing cell E5.
- 2. Select the destination cell: Click the cell where the calculated result should appear, such as F5.
- 3. Enter the conditional rounding formula: Type =IF(E5>80,0,CEILING.MATH(E5,10)) in the formula bar and press Enter.
- 4. Apply the formula to more rows: Select the formula cell and drag its fill handle downward to copy the formula to adjacent rows.

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.




