Round if decimal number ends with
This tutorial shows how to round if a decimal number ends with a specific number using an Excel formula, with the IF, VALUE, RIGHT, TEXT, TRUNC and ROUND functions
Hard coded formula
Cell reference formula
|
GENERIC FORMULA
=IF(VALUE(RIGHT(TEXT(value-TRUNC(value),"General"),1))=end_value,ROUND(value,0),value)
ARGUMENTS GENERIC FORMULA
=IF(VALUE(RIGHT(TEXT(value-TRUNC(value),"General"),1))=end_value,ROUND(value,0),value)
ARGUMENTS 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 TEXT function is then used to convert the derived number into a text value with the text format of "General". The RIGHT function, with the number of characters set to 1, then returns the last value from the converted decimal number. The VALUE function then converts the text value into a number value. Finally, the IF function then tests if the derived number, which represents the last decimal number, is equal to the specific value. If the test is TRUE, meaning that it equals 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.
Click on either the Hard Coded or Cell Reference button to view the formula that has the ends with 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 decimal number is equal to | How to round if a 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 | |
| VALUE Function | The Excel VALUE function converts a text string that comprises numbers into a numeric value | |
| RIGHT Function | The Excel RIGHT function returns the specified number of characters from a specified string, starting from the right side | |
| TEXT Function | The Excel TEXT function returns a numeric value as text, in a specified format | |
| ROUND Function | The Excel ROUND function returns a rounded number in accordance with the specified number of digits |