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

it is nice tutorial thanks…pls what is difference between float and interger value ?

Very helpful video Sumit. MROUND is my favorite rounding function. Thanks for creating this video!

Thank you so much. I got tired by remembering this formula. It is very useful.

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

https://www.youtube.com/channel/UCLwQKEVv1aFXCCnIkJ5LOoA

Very helpfull thankyou…

Thank u very much Boss. its so useful

SUPER SIR…..

thanks a lot.

THANKYOU, VERY USEFULL

Thanks! very useful small tricks.

THANK YOU BROTHER!!

thx

Thank you.

This saved me approx ROUNDUP(7,5;10,0) Asperines.

thanx buddy i was always trying to find the rounding to .5 …… cheers

Thank you