Round if number of decimal places is equal to

This tutorial shows how to round a number if the number of decimal places is equal to a specific number using an Excel formula, with the IF, LEN, FIND and ROUND functions

EXCEL FORMULA 1. Round if number of decimal places is equal to a specific value

EXCEL

Hard coded formula
Round if number of decimal place is equal to
Cell reference formula
Round if number of decimal place is equal to
=IF(LEN(5.472)-FIND(".",5.472)=3,ROUND(5.472,0),5.472)
=IF(LEN(B7)-FIND(".",B7)=$C$4,ROUND(B7,0),B7)
GENERIC FORMULA

=IF(LEN(number)-FIND(".",number)=equal_to,ROUND(number,0),number)

ARGUMENTS
number: The number that you want to round if the number of decimal places is equal to a specific number.
equal_to: The number of decimal places is equal to.

GENERIC FORMULA

=IF(LEN(number)-FIND(".",number)=equal_to,ROUND(number,0),number)

ARGUMENTS
number: The number that you want to round if the number of decimal places is equal to a specific number.
equal_to: The number of decimal places is equal to.

EXPLANATION

This formula uses the FIND function to return the position of a full stop from a number. The LEN function is used to count the total number of characters that exist in the selected number. The formula then subtracts the position of a full stop from the total number of characters to return the total number of decimal places. The formula then uses the IF function to test if this amount is equal to the specific number and if it does, meaning the number of decimal places from the selected number is equal to this specific number, the formula will round the entire number using the ROUND function. If the IF function returns a FALSE value, meaning the number of decimal places don't equal to the specific 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 number of decimal places that a number is equal to 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 nth decimal number is equal to How to round a number if the nth decimal number is 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
FIND Function The Excel FIND function returns the position of a specific sub-string within a string
LEN Function The Excel LEN function returns the number of characters in a specified string
ROUND Function The Excel ROUND function returns a rounded number in accordance with the specified number of digits