Round if first decimal number is greater than
This tutorial shows how to round if the first decimal number is greater than a specific number using an Excel formula, with the IF, VALUE, MID, FIND and ROUND functions
Hard coded formula
Cell reference formula
|
GENERIC FORMULA
=IF(VALUE(MID(number,FIND(".",number)+1,1))>greater_value,ROUND(number,0),number)
ARGUMENTS GENERIC FORMULA
=IF(VALUE(MID(number,FIND(".",number)+1,1))>greater_value,ROUND(number,0),number)
ARGUMENTS EXPLANATION This formula uses the FIND function to return the position of a full stop from a number and then adds one to return the position of the first number after the full stop, which represents the first decimal number. The MID function is then used to return the number with a position calculated by the FIND function. This will return the first 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 first decimal number, is greater than the specific value. If the test is TRUE, meaning that the first decimal number is greater than 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 greater than the first 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 first decimal number is equal to | How to round if the first 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 | |
| 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 |