Automate rounding of decimal numbers MS Excel

Our Excel training videos on YouTube cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday.
http://www.familycomputerclub.com
When you work with numbers in MS Excel you’ll often have decimal numbers which need to be rounded off because seldom do you need a precision upto 4 0r 5 decimal places. You can achieve the round-up or round-down in a variety of ways. The method described here to automate…

41 thoughts on “Automate rounding of decimal numbers MS Excel”

1. sir, it is worth tutorial … very helpful Thank you .

2. Sir, in Indian Currency suppose If a amount is Rs. 35.315, it's shows in excel that Rs. 35.31, but I want the result is Rs. 35.32. how can i do it? Is there any formula?

3. I appreciate your work….. thanks a lot SIR.
but there is another way for automatic round off i just got brain wave about it at mid night…today

formula ==ROUND(IF(O23=0,0,O23),0),where 023 is cell no.

IT WORKS EVEN IF CELL CONTAINS OTHER FORMULA

4. Hello Dinesh Kumar ji,
I want to apply this formula to a cell which has already added formula of total. PLzz reply

5. Thanks for this video. Saved my time and exactly got the result I wanted. 🙂

6. Hi Mr. Dinesh Sir,

Sir my question is

I want to use rounding of decimal numbers in my subtotal how can i use it,

=SUM(H12:H50)
amounting is (45,893.58)
how can i use rounding of decimal numbers,

I am also using number to words formula on my subtotal and i don`t want to write cents in my formula (.58)

please sir help me in this matter.

7. Sir
how to count 0.5 in automatic calculated in excel in the sequence way

8. Hi Mr. Dinesh,
How do you solve the following in Excel 2007:
0.01 – 0.85 round down to 1
>0.86 round up to 1

9. How to do your round to the nearest hundredth? For example, 4.495. I would like to round down to 4.49.

10. That was crazy! Very helpful — thank you for taking the time to post this!

11. This is exactly what I want to know.   very useful!!

12. Sir, for the above example you have shown for a particular cell, if I have to convert the entire column of data into 3 decimal figure in one go using macros how to proceed.

13. dear sir I need your help if I have number as example 230 I wanna to know how tens and odds in it by simple calculations kindly can you help

14. amazing thank you so much for this video

15. Really many many thanks from my heart for the wonder tools. It reduces my 3 hours work. thanks again

16. my goodness, this was extremely helpful. Thank you.

17. How about rounding down numbers instead of decimals. Is it possible?
For example 288 to 280
53 to 50  (First digit)
and if it is, then is it possible to add it to a formula? For example =A1*B1……
I've found it =ROUNDDOWN(A1;-1) for first digit :)))

18. I agree with Shradhanand Tidake; using formula =ROUND(A1,0) also determine ROUNDUP AND ROUNDDOWN amount by dragging/copying the formula down (auto-fill) as exactly you do, but using functions MOD, Ceiling and Floor is complicated and long process.

Thank you for sharing.

19. is it possible to round down on values of .6 down and from .61 up without having to do one by one

20. Thank you very much
I understand that the outcome could of been completed in a shorter procedure but its great to see you hitting two birds with one stone

21. Y so complicated??   just use ROUND function.   FOr you example: =ROUND(A1,0)    gives 25.

22. sir, i want to round up to the nearest multiple of 3. how can i do that in excel 365

23. Agreed, m just saying that while sharing such solutions lets share the alternates too. At-least the ones which are easy to remember. M sure ceiling and floor function would come handy in some complex situation.
But i suppose automation is all about making things simple and easy to remember and understand
Thanks 🙂 keep sharing 🙂

24. Nice formula.
However same thing could have been done in more simple way.
Just use this formula instead, =ROUND(A1,0). This formula will convert the number into nearest zero decimel number. For e.g. 2.01 to 2 and 2.52 to 3

25. Is it possible to round down numbers with decimal equal to or smaller than .25 and round up numbers with decimal larger than .25? Many thanks

26. Hi I want to round off the following number.. example.

45.1 should be round off to 45
45.2 should be round off to 45
45.3 should be round off to 45.5
45.4 should be round off to 45.5
45.5 should remain its number to 45.5
46 should also remain its number to 46
46.1 should be round off to 46
46.2 should be round off to 46
46.3 should be round off to 46.5

I hope you get the idea and you can help me.. Thank you

27. is it possible to roundoff like e.g. from 1.2121 into 2 or from 23.4 to 24