Round if nth decimal number is equal to

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

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

EXCEL

Hard coded formula
Round if nth decimal number is equal to
Cell reference formula
Round if nth decimal number is equal to
=IF((IF(5.748=INT(5.748),0,LEN(5.748)-FIND(".",5.748,1)))>=2,IF(VALUE(MID(5.748,FIND(".",5.748)+2,1))=4,ROUND(5.748,0),5.748),5.748)
=IF((IF(B8=INT(B8),0,LEN(B8)-FIND(".",B8,1)))>=$C$5,IF(VALUE(MID(B8,FIND(".",B8)+$C$5,1))=$C$4,ROUND(B8,0),B8),B8)
GENERIC FORMULA

=IF((IF(number=INT(number),0,LEN(number)-FIND(".",number,1)))>=nth_decimal,IF(VALUE(MID(number, FIND(".",number)+nth_decimal,1))=equal_to,ROUND(number,0),number),number)

ARGUMENTS
number: The number that you want to round if the nth decimal number is a specific number.
equal_to: The value that the nth decimal number equals to.
nth_decimal: The nth decimal number that you want to test for.

GENERIC FORMULA

=IF((IF(number=INT(number),0,LEN(number)-FIND(".",number,1)))>=nth_decimal,IF(VALUE(MID(number, FIND(".",number)+nth_decimal,1))=equal_to,ROUND(number,0),number),number)

ARGUMENTS
number: The number that you want to round if the nth decimal number is a specific number.
equal_to: The value that the nth decimal number equals to.
nth_decimal: The nth decimal number that you want to test for.

EXPLANATION

This formula uses the FIND function to return the position of a full stop from a number and then adds a number that represents the nth decimal number that you want to return. The MID function is then used to return the number with a position calculated by the FIND function. This will return the nth decimal number from a selected number. The VALUE function then converts the value into a number value. Finally, the IF function then tests if the derived number, which represents the nth decimal number, is equal to the specific value. If the test is TRUE, meaning that the nth decimal number is equal to the specific number, the formula will round the entire number using the ROUND function, alternatively it will return the exact number that is being tested, without rounding it. At the beginning of formula the IF, INT, LEN and FIND functions are used to count the number of decimal places to identify if there are more than the n number of decimal places, otherwise the formula will return an error if it contains less decimal places then the nth decimal place that the formula is looking for.

Click on either the Hard Coded or Cell Reference button to view the formula that has the nth decimal value, the equals to 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
Return nth decimal number How to extract the nth decimal 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
LEN Function The Excel LEN function returns the number of characters in a specified string
VALUE Function The Excel VALUE function converts a text string that comprises numbers into a numeric value
FIND Function The Excel FIND function returns the position of a specific sub-string within a string
MID Function The Excel MID function returns the specified number of characters from a selected string, starting at a specified position
ROUND Function The Excel ROUND function returns a rounded number in accordance with the specified number of digits