Formula Errors

How to Round Odd and Even Values Differently in Excel

Natalie Taylor Oct 10, 2026 870 views

Question details

The user needs a custom formula to round values ending in .50 differently based on the preceding digit: rounding up if the first decimal digit is odd, and rounding down if it is even.

How to Round Odd and Even Values Differently in Excel

Product: Excel
Device & OS: not provided
Scenario: Applying specialized rounding rules to a dataset where standard rounding or default banker's rounding does not meet the specific mathematical or accounting requirements.
Observed behavior: Implement a custom rounding logic to one decimal place, ensuring values like 14.150 round to 14.2, and 14.250 round to 14.2.

Before you start

Ensure your target cells are formatted as Numbers rather than Text, and have a test set of data containing exact .50 endings (like 14.150 and 14.250) ready to verify the logic.

Solution 1Recommended

Use a combination of IF, ISEVEN, MOD, and ROUND functions

This formula isolates the first decimal digit and checks if it is even. It applies a conditional ROUNDDOWN or ROUND depending on the odd/even status.

Standard Excel rounding functions uniformly round halfway values (.5) up, which does not accommodate this specific requirement. By nesting ISEVEN and MOD inside an IF statement, you can manually dictate the rounding direction for even and odd decimal integers.

Select the destination cell

Click on the cell where you want the rounded result to appear (e.g., cell B1).

Enter the custom formula

Type the formula: =IF(AND(ISEVEN(A1*10),ROUND(MOD(A1*10,1),2)<=0.5),ROUNDDOWN(A1,1),ROUND(A1,1)) and replace A1 with your actual data cell reference.

Apply to the rest of your data

Press Enter to calculate the result. Then, drag the fill handle at the bottom-right corner of the cell down to apply this custom rounding formula to the rest of your column.

Use a combination of IF, ISEVEN, MOD, and ROUND functions

Test your logic: Always test the formula against known values first. For example, check that 14.150 properly rounds to 14.2 (odd digit rounds up) and 14.250 rounds to 14.2 (even digit rounds down).

Solution 2

Use the LET Function (For Microsoft 365 Users)

The LET function simplifies complex formulas by allowing you to assign names to calculation results, making the formula much easier to read and edit.

Advanced Formula Support

Master Custom Formulas with WPS Office

WPS Spreadsheet fully supports advanced logical and mathematical functions such as IF, ISEVEN, MOD, and LET. You can smoothly execute these custom rounding formulas exactly as you would in Microsoft Excel, completely for free.

1. Download and Install: Download WPS Office for free from the official website and complete the quick installation.
2. Open your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook.
3. Apply Formulas: Paste the provided rounding formulas into your cells to effortlessly analyze and process your dataset.

100% format compatibility with Microsoft Excel (.xlsx) filesFull support for over 400 standard and advanced spreadsheet formulasLightweight installation and incredibly fast data processingFree to download and provides a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between standard rounding and banker's rounding?

Standard rounding generally rounds halfway values (.5) up to the next integer (e.g., 2.5 becomes 3). Banker's rounding, commonly used in accounting, rounds halfway values to the nearest even number (e.g., 2.5 becomes 2, and 3.5 becomes 4) to eliminate statistical bias in large datasets.

Can I adjust this formula to round to two decimal places instead of one?

Yes. To round to two decimal places using the standard IF method, you must adjust the multiplier in the formula from 10 to 100, and change the decimal arguments within the ROUND and ROUNDDOWN functions from 1 to 2. The LET formula makes this easier by just changing the 'Places,1' variable to 'Places,2'.

Why do we use the MOD function in this rounding formula?

The MOD function returns the remainder after a number is divided by a divisor. In this formula, MOD is used to isolate the decimal portion of the number, allowing the formula to pinpoint exactly when the decimal part is exactly .5 or halfway.

Excel & Data Problems