Round if decimal number doesn't equal to

This tutorial shows how to round if a decimal number doesn't equal to a specific number using an Excel formula, with the IF, ABS, TRUNC and ROUND functions

EXCEL FORMULA 1. Round if decimal number doesn't equal to

EXCEL

Hard coded formula
Round if decimal number doesn't equal to
Cell reference formula
Round if decimal number doesn't equal to
=IF(ABS(5.4-TRUNC(5.4))<>0.4,ROUND(5.4,0),5.4)
=IF(ABS(B7-TRUNC(B7))<>$C$4,ROUND(B7,0),B7)
GENERIC FORMULA

=IF(ABS(value-TRUNC(value))<>doesn't_equal_value,ROUND(value,0),value)

ARGUMENTS
value: The value that you want to round if it doesn't equal to the decimal number.
doesn't_equal_value: The not equal to decimal number.

GENERIC FORMULA

=IF(ABS(value-TRUNC(value))<>doesn't_equal_value,ROUND(value,0),value)

ARGUMENTS
value: The value that you want to round if it doesn't equal to the decimal number.
doesn't_equal_value: The not equal to decimal number.

EXPLANATION

This formula uses the TRUNC function to return the truncated number of the number that is being tested. It then subtracts that number from the exact number that is being tested to return the decimal value of the number that is to be rounded. The ABS function is used to return the absolute number, therefore, returning the result as a positive number. The IF function is then used to check if the decimal number derived through the use of the ABS and TRUNC functions doesn't equal to the specified decimal number. If the IF function returns a TRUE value, meaning that the number that is being tested is not equal to a specific decimal number, the formula will round the number through the use of the ROUND function. If the number is equal to the specific decimal number the formula will return the exact number that is being tested, without rounding it.

Click on either the Hard Coded or Cell Reference button to view the formula that has the not equal to decimal value and the number that is being tested directly entered into the formula or referenced to specific cells.

RELATED TOPICS

Related Topic Description Related Topic and Description
Round if doesn't equal to How to round a number if it doesn't equal to a specific number
Round if decimal number is equal to How to round if a decimal number is equal to a specific number
Round if decimal number is greater than or equal to How to round if decimal number is greater than or equal to
Round if decimal number is less than or equal to How to round if a decimal number is less than or equal to a specific number

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
ABS Function The Excel ABS function returns the absolute value of a number
ROUND Function The Excel ROUND function returns a rounded number in accordance with the specified number of digits