Automating rounding of decimal numbers in MS-Excel
How to automate rounding of decimal numbers in MS-Excel
Help me in Microsoft Excel?
In Microsoft Excel I want to get the number to be rounded to the nearest number for eg: 25.678 to 26
or 25.423 to 25
I tried searching the roundup , round-down , round functions but they only truncate the decimals.
Am using Windows 7 and Microsoft 2007 Excel.
The simplest way to round numbers in MS-Excel is to use the in-built round-up or round-down functions. These functions do not help in automation since the user has to decide how to round the decimal in every individual case.
We can use the Roundup function to get 27 by rounding up 26.6, for example.
Similarly we can use the round-down function to get 26 from 26.4. But every time the user has to tell the computer what to do and how to do the rounding.
You can also use the round function to get 26 from 26.4 and 27 from 26.5 when you round both the numbers and specify the number of digits as 0. But again you cannot automate the process.
To find out whether a number has greater than or equal to 0.5 in its decimal side we can use the MOD function. We take our number in the MOD function and use 1 as the divisor. This gives us the value of the decimal part of our number. For example if you write ‘=MOD(A1,1)’ in cell B1 and the value in cell A1 is 26.72 then you will get the value of the remainder i. e. 0.72. Now using the IF function we can check whether the remainder is greater than or equal to 0.5. Once that is determined we can use the CEILING function to give us 27 for a value of 26.5 and above. If the value of the remainder is below 0.5 we ask Microsoft Excel to use the FLOOR function to give us 26.
Therefore combining the ‘IF’ function with MOD, CEILING and FLOOR functions we can automate the complete process.
The formula will now look like this:
The formula is read as: If the division of the number in cell A1 by 1 gives a remainder greater than or equal to 0.5, then roundup the value else round-down. The formula’s result would be 27 in case of values greater than or equal to 26.5 and 26 for value less than 26.5.
What each of the function MD, CEILING and FLOOR does with the Syntax is given below:
MOD returns the remainder when the number is divided by a divisor. MOD(5,2) returns 1, for example.
CEILING returns the rounded up number. For example, CEILING(3.5,1) returns 4.
FLOOR rounds down the number. For example, FLOOR(3.5,1) returns 3.
Watch the video below to learn how to automate the rounding of decimal numbers: