How to Round Numbers to the Nearest Integer or 0 5 or 5 or 10 in Excel

How to Round Numbers to the Nearest Integer or 0 5 or 5 or 10 in Excel


Hello and welcome to the video by Trump
Excel. I am Sumit Bansal and in this video, I am going to show you how to
round numbers to the nearest integer or to the nearest multiple of 0.5 or 5 or
10. Now here is a simple data set. Let’s suppose you are a project manager
and you have these five projects and you do effort estimation and you come up
with these number of hours that will take for you or your team to complete
these projects and the client has given you these many days so based on this you
calculate the total number of full-time equivalents or the total number of
resources needed for each project so it’s a simple calculation where I have
divided this number by 3 into 8 because I am considering an eight-hour workday
so I have these numbers but the problem here is that I cannot really allocate
6.71 resource or 6.71 employees on a project I need to allocate 7 similarly
here I need to allocate 5 people sorry 6 people so what I need to do is I
need to round these numbers to the next integer now there are a couple of
functions to do this and if you are thinking about the int function int
function it would work but the problem with this
function is that it would simply truncate the decimal part and give you
the interior portion of this so for example if I do this then it will simply
truncate the decimal part but this is not what I want I want the next integer
value so in this case what I would have to use is the round up function so round
up function takes two arguments the first one is the number which would be
this in this case and the second one is the number of digits and in this case I
want this to be rounded up to 0 number of digits which means that I do not want
any decimal portion I only want the integer and I want this to get rounded
up so if I enter 0 here and I hit enter you can see I get 7 here and if I copy
it for all these cells I get the correct value I get 7 here 6 8 8 and
so this is how you can round to the nearest integer around up to the nearest
integer if you only want to round to the nearest integer let’s say for example if
it’s okay if it’s seven point two nine and you can get away with
allocating only seven resources on this then you can use the M round function
and M round function would round to the nearest based on whether it’s rounding
up or rounding down based on the closest integer or the closest number of the
multiple that you specify so in this case since the number is this I will use
t2 and the multiple here would be one and now when I hit enter see what
happens this becomes seven this becomes six but in this case there is a
difference because this is seven point two nine which is closer to seven but in
this case seven point five it is closer to it so M round would round to the
nearest integer or the nearest multiple that you specify similarly let’s see
another example here I have the same data set and in this case what we can do
is instead of rounding it up to the nearest integer as a project manager you
may think that you’ll make an employee work for half a day so what you can do
is you can round this to the nearest multiple of 0.5 and to do that we will
simply use the M round function as I showed you a couple of seconds ago so
this would be the number and the multiple here would be 0.5 but let’s see
what happens if I use this it will give me all these numbers which will round it
up to the closest multiple of 0.5 but in case you only want to round it up then
you can use the ceiling function so the ceiling function is this one you have
two functions here ceiling dot math and ceiling they both do the same thing it’s
just that this one is the old function where you see this yellow triangle and
this one is the new one so if you seeing there a ceiling dot math function do you
use this one and in this case the first would be the number the first argument
would be the number which is this and then it will ask for the significance
value in this case the significance value
would be 0.5 and now when I hit enter you can see it’ll give me 7 and it’ll
give me five point five but here is the difference M round would give me the
closest multiple number which would be seven point five but since I am using
the ceiling function which would it would round it up to the closest
multiple of point five which is eight so you can see you can do ceiling dot math
similarly if you wanted to round it down then you can use flow dot maths so here
it’s floor we type this again floor mat and here is the number and the
significance is 0.5 so in this case it’ll round these numbers down to the
nearest multiple of point four not the nearest multiple the load the lower
multiple of 0.5 so you can use em round you can use ceiling or you can use floor
depending on what you want to get done now it’s a very simple example now if
you want to round this to the nearest five you can simply use M wrong this is
the number the multiple is five and let’s see what it gives us it gives us
these results because these are closest to those numbers 161 is closest to 160
let’s see what happens when I change this to 163 it gives me 165 because now
163 is closer to 165 the difference is only two if you want to always round
these up then you can use the ceiling method again let me quickly show it to
you ceiling math this is the number and the
significance is five and you can see there is a difference here because in
132 if you use M round it will W 130 because that’s the nearest multiple of
five but in ceiling it will always take this number to the next number which is
a multiple of five similarly if you want to go down you can use floor mat floor
mat this is the number significance is five and it will give you the number the
multiple which is the lower one which is lower than this number and similarly in
case of nearest ten you can use two kinds of formula so let
me first quickly show you Emraan the brown as we all know would do it to the
nearest one so in this case the multiple would be ten so this is the result of M
round let me type M round here now again we
can use ceiling or floor so let’s quickly see the results of ceiling and
floor and I’ll show you what a new formula or a new way of doing this so
let’s quickly use ceiling mat this is the number the significance is 10 so it
will give us the next multiple and in this case it will give us the previous
model or the lower number 10 now another way in case you want to round to the
nearest 10 or nearest hundred another way of doing it is using the round up or
round down function so let me quickly show you how these work in case of round
up this is my number but if I want it to get round up by the multiple of ten then
I can use minus 1 so 0 is when it’s not a multiple when there is no multiple in
case of round up but if I use minus one it will go one step back and it will
consider it as 160 so it will be a multiple of 10 so see what happens he’ll
give me 170 which would be the same result as ceiling in this case and
similarly I can also use a round down so if I come here round down and I can
again use the same thing minus 1 and minus 1 would give me the multiples of
10 if I make it minus 2 it will give me the multiples of 100 minus 3 would give
me the multiples of thousand so you can also use a round up or you can use a
ceiling I I recommend you use ceiling because this function is typically made
for this so there are three ways of doing it you can either use
round ceiling of floor and then there is roundup roundup which would work only in
the case when you are rounding it of it off to the nearest ten or nearest
hundred or nearest thousand so these are the ways to round numbers to the nearest
integer or to the nearest multiple in Excel that’s it in this video I hope you
found this useful thank you and have a nice day

17 thoughts on “How to Round Numbers to the Nearest Integer or 0 5 or 5 or 10 in Excel”

  1. Awesome. Have seen this video after watching video from lyda and i feel like you explain it in much easier and logical way……two thumbs up for you

Leave a Reply

Your email address will not be published. Required fields are marked *