The tutorial explains the uses of ROUND, ROUNDUP, ROUNDDOWN, FLOOR, CEILING, MROUND and other Excel rounding functions and provides formula examples to round decimal numbers to integers or to a certain number of decimal places, extract a fractional part, round to nearest 5, 10 or 100, and more. Continue reading
Comments on: How to round numbers in Excel: ROUND, ROUNDUP, ROUNDDOWN functions
by Svetlana Cheusheva, updated on
Comments page 8. Total comments: 375
formula that give me results as:
4.45 = 4.4
4.55 = 4.6
4.05 = 4.0
x.yz
if y = 0, 2, 4, 6, 8 ( even ) disregard z
if y = 1,3,5,7,9 (odd) add 1 on y.
if z 5 add to y
Hi Svetlana Cheusheva!
It was very useful to solve my requirement. Excel is an ocean we agree.
Knowledge is all and it is worthy too.
Thanks again for Svetlana Cheusheva and also other friends sharing your knowledge here.
With Great Thanks.
Alexos.
Hi There , i need to round ex:453 , 458 to nearest 5 or nearest 10 . any help thanks
I need to change dollars and cents to whole dollars and multiply by certain percentages and the resulting answer should not be in whole dollars; eg:
$33350.97 becomes $33,350*35% = $11672.50 (rounded to 2 decimal places)
how to round off time only if minute is greater then or equals to 30 to the next(higher hour)
ex.01:35 to 02:00 hrs
01:45 to 02:00 hrs
but 01:29 then as it is 01:29 hrs
please help
Hi,
Roundup Value method (at the time of multiplying)
62056*1.5%=930.84 apply formula as =ROUNDUP(62056*1.5%,0)
or =ROUNDUP(930.84,0)
62056*1.6=99,289.60 apply formula as =ROUNDUP(62056*1.6,0)
or =ROUNDUP(99289.60,0)
(Put = & TYPE roundup ( Open bracket type/link the value and * with
percentage or number and add ,0 then close the ) bracket and press
enter.
IF this Result should be
350.1 350
350.5 350
350.9 350
351 360
351.5 360
359.9 360
360 360
basically if my ones is Zero i want to rounddown and if the ones is 1to9 then tens will be rounded up...
suggest the formula
Please try the formula below:
=(INT(A1)-RIGHT(INT(A1),1))+IF(RIGHT(INT(A1),1)="0",0,10)
Hope this will help.
how to create formula for
82.01 & 82.02= 82.00 or 0.01-0.02=0.00
82.03 & 82.04= 82.05 or 0.03-0.04=0.05
82.06 & 82.07= 82.05 or 0.06-0.07=0.05
82.08 & 82.09= 82.10 or 0.08-0.09=0.10
please help me please...
Hi There,
Anyone can help me? for " positive number", it would be round up and for " negative number", it would be round down with "no".
example 307/30 roundup 11
-15/9 rounddowwn -2 but i wanna see "no"
Hi, I have problem here. How do i round number like below
0.0 to 0.0
0.1 to 0.0
0.2 to 0.0
0.3 to 0.0
0.4 to 0.0
0.5 to 0.5
0.6 to 1.0
0.7 to 1.0
0.8 to 1.0
0.9 to 1.0
1.0 to 1.0
Can anyone help?
hi there!
im having a problem on how to put "if the tenths digit number is ODD;roundup and if the tenths digit number is EVEN;rounddown" in excel formula. thanks in advance!
example:
24.45= 24.4
24.15= 24.2
Hello,
Please try the following formula:
=IF(ISODD(MID(A1,FIND(".",A1)+1,1)),ROUNDUP(A1,1),ROUNDDOWN(A1,1))
Hope it will help you.
Hello,
I need a formula for below issue
12.45 = 12.45
12.23 = 12.23
12.51 = 13
12.79= 13
How can i round up number such as:
0.00 to 0.00
Below 0.01 to 0.00
above 0.01 to 1.00
how to 4165*10% round formula
Hi. I am trying to round the difference between 2 times to the nearest 15 min increment. I know I can use MROUND to round the times and then do the difference but trying to see if I can just do it on the difference. Thanks
Jay
After a few moments of play, I believe this accomplishes what I want.
{=SUM(ROUND(IF(ISNUMBER(A1:A5),A1:A5,0),0))}
Thank you for this site, which got me thinking about a solution.
When summing rounded values, as in:
{=SUM(ROUND(A1:A5,0))}
some cells may not contain a number value (common case as in "N/A").
Any thoughts whether it is possible to add validation as number prior to the round function, so that the formula does not return #VALUE!? Essentially, eliminate non-numeric values, before rounding each, and then summing.
Hello,
Thank you for the helpful information. I just wanted to point out that under the "TRUNC Function", in the screenshot example, the third row's formula should be "=TRUNC(A3,1)" in order to get the number in B3. Just don't want others to get confused.
Thanks,
Steven
Hello Steven,
Thank you for pointing that out! Fixed.
Hi,
Is there a way to round up an input in the same cell without adding a new column for the return result? eg, data validation?
I want to know the formula which decimals not to calculate while sum total
if
1.5 1.5 1.5 1.5 1.5 1.5 --> 6 to calculate with out decimal caculateing
Hey, does anyone know how I can round up or round down an equation?
(A1-10)/2
=ROUND((A1-10)/2,0)
The 0 is for no decimal places. replace it with 2 for 2 decimal places or maybe with -1 to round to the nearest 10.
Hi,
is there anybody who knows how I can round the calculation result to end in -9
I have a forumula (D2 + $H$2 +$H$4 + $H$5)the result of which i want to end in number -9
bgrds
Dodo
Hello, how do i formulate a cell for the figures to end in a certain number (ex. 195, 295) and not in multiples of 95?
Hello, I want to roundup cell 2564 =2560 and I want to roundup the 2564 =2570 in same cell and How can I do this task.
I need to Round the following lines to the nearest 1/16. But Excel will not allow this. for example,
IF(D9=2,D5/2,
I may get 83.3333 but I need 83.125.
=IF(OR(D3=0,D3=""),"",
IF(D9=1,D5,
IF(D9=2,D5/2,
IF(D9=3,D5/3,
IF(D9=4,D5/4,
IF(D9=5,D5/5,
IF(D9=6,D5/6,
IF(D9=7,D5/7,""))))))))
I am needing to found any thing above 100 back down to 100. How do I write this formula?
Hi,
Which formula used for the round in the multiple of 10 for both figure( Negative as well as positive).
Mround is used only for the positive figure and rounddown is for negative figure but i want 1 formula for both the figure.
Is there any???
Please tell me............
Hullo, I want to roundup cell I3 which carries an amount 2100 and I want to roundup the same as 3,000 in another cell and How can I do this task.
whole numbers such as 201
202
203
204
205
206
207
208
290
210
1-2 round down ie 201 to 199 202 to 199
3-6 rounded to 5
202 203 204 205 206 become 205
207 208 209 210 become 209
anyone help?
Good afternoon,
I'm needing to add 2 different percentages to a formula and they both need to round. I will need to round the first percentage before i can add the second percentage to that number. Right now excel will not allow me to round each percentage but will allow me to round both percentages at the end.
example:
this is what i need to happen in my formula
218*-15%= -32.7 (round up)
218-33=185
185*8%=14.8 (round up)
185+15=200
200+205=405
405 is the number i need to reach
and this is my formula in excel now:
A3=218
B3=-15%
C3=8%
D3=205
=ROUND(A3*B3+A3*C3,0)+A3+D3 and that comes up to 408, which is 3 over from 405
Before, i needed to only add one percentage and the round formula works great but i cannot find a way to get it close. I have tried with a larger number, replace A3(218) with 1304 and the gap was larger and will cause problems.
example:
it should figure out to 1402
1304*-15%=195.6 (round up)
1304-196=1108
1108*8%=88.64 (round up)
1108+89=1197
1197+205=1402
And in excel, with the same formula as above, i get 1418. I know with percentages you will have numbers be maybe 1 or 2 off due to rounding but this is too great and will not work.
Please let me know if there is another way to write this formula to make it work properly.
Thanks.
well i found a formula that worked
(ROUND((8%+1)*(ROUND(218*-15%,0)+218),0))+205=405
just fyi...
Hi, Can hi you help me to get this?
I can't really came up with a solution for this problem
say I want 1mins-4mins rounded into 0 and 5mins-19mins rounded into 15mins then 20-34mins rounded into 30mins and 35mins-1hr4mins rounded into 1hr/60mins
i hope yo ucan help me with this thanks :)
Please help me get the rounddown or roundup for the following:
a) 1.01 - 1.85 round down to 1
b) more than 1.86 round up to 2
=ROUND(B1/10^TRUNC(LOG(B1)),A1-IF(B1>1,1,0))*10^TRUNC(LOG(B1))
I prematurely posted a significant digit rounding I've been using. Sediment is what I deal with and it is the size of clay in mm to cobble in mm, where significant digits count. I don't know of a function to do this so I wrote this formula; =ROUND(B1/10^TRUNC(LOG(B1)),A1-IF(B1>1,1,0))*10^TRUNC(LOG(B1)) with cell A1 the significant digits and B1 the input number to be rounded.
Hi Team,
I want a simple formula for round-off, like -
a) 0 Years, 9 Months, 15 Days - Should be rounded off to 9 Months
b) 0 Years, 9 Months, 16 Days - Should be rounded off to 10 Months
means, anyone, below 15 or equivalent to 15 days would remain 09 Months and
anyone, greater than or equivalent to 16 days would be 10 months.
Thanks & regards
Arnab Sarkar
In cell A1 have 10 and cell B1 is 8.50681, when I multiply A1*B1 result appear is "85.0681", But I show in Cell B1 is 8.50 till (2 decimal point). And now I want to multiply A1*B1, and I want that excel count till 2 decimal point like 8.50 in B1. Is it possible? remember B1 actual value is 8.50681.
what i have to do if bulk amount need to round off, if the amount will >5 so it will grow upside,if the amount <5 so it will get down side
How to convert upper than 15 digit value in excel
I am having a problem using ceiling function.
Now my Problem is : I want to ceil few values upto 0.5 and then rest upto 0.25 in the SAME CELL !! like if i want to ceil values between 2 and 3 upto 0.5 i.e if i write 2.3 it gives me 2.5 or if i write 2.74 it gives me 3. But in the same cell if i write values between 0.5 to 2, it should ceil me upto 0.25 i.e. if i write 1.2 it should give me 1.25 or if i write 1.67 it should give me 1.75. Hoping for a reply. Thanks for the concern.
Can you explain why this happens?
For the same input of 0.01, the spreadsheet should not be giving .02, .02 is incorrect. But why? Thanks.
=ceiling(0.01,0.01) = .01
=ceiling(16.00-15.99,0.01) = .01
=ceiling(16.01-16.00,0.01) = .02
=roundup(0.01,2) = .01
=roundup(16.00-15.99,2) = .01
=roundup(16.01-16.00,2) = .02
can I round up years? for example 1 years, 3 months, 4 days, rounded up to 2 years.
Could you give me the formulation? thanks.
Is there a way to force excel to round the following decimal down, instead of up? $2.225. I want it to round any number where the third decimal place is a "5" (and only a 5, not any other number) down, instead of up.
For instance: $2.225 to $2.22
$2.226 auto rounds to $2.23, this is fine
$2.224 auto rounds to $2.22, this is fine
Is there any way to round a column of currency to an even number (reducing a dollar amount by $.01)
$887.65 to $887.64 leaving the even numbers as is.
I can easily round up. I'm not finding a way to round down.
Hi Julie,
To round down to the even hundredth, you can use the FLOOR function with multiple 0.02, e.g.:
=FLOOR(A1, 0.02)
maam, how to round off even a minute to the next(higher hour) ex.01:05 to 02:00 hrs please help
0
Hi Kandan,
You can use any of the following formulas:
=CEILING(A1, TIME(1,0,0))
=CEILING(A1, "1:00")
=ROUNDUP(A1*24,0)/24
Hi, I want round off this time,1:16 to the nearest 5 minutes,anybody can help me
Hi Masooda,
To round time to the nearest 5 minutes, you can use the MROUND function with multiple equal to "0:05", e.g.:
=MROUND(A1,"0:05")
Can you explain why this happens?
=ceiling(0.01,0.01) = .01
=ceiling(16.00-15.99,0.01) = .01
=ceiling(16.01-16.00,0.01) = .02
=roundup(0.01,2) = .01
=roundup(16.00-15.99,2) = .01
=roundup(16.01-16.00,2) = .02
Hey - I wondered if you might help. I'm trying to work on membership rates and wanting to round down to $1,500 if the formula we use surpasses that mark. Any ideas people? Many thanks in advance.
Hi Richard,
You can embed your formula into the IF function, like this:
=IF(formula()>1500, 1500, formula())
if i have a function that if c1=8 and c1 cells contains value 8 and then i create this that if the value is true then i will forward that if it is true then it take the value of cell d1 which value is 2x1.8 then how i will solve this that it gives value 3.6 automatically not the value same 2x1.8 in the cell where i put this formula
if i have a function that if c1=8 and c1 cells contains value 2x1.8 then how i will solve this that it gives value 3.6 automatically not the value same 2x1.8 in the cell where i put this formula
Very helpful it was required while working and finally I got the solution and the result...
Thank you...
Hi
Firstly great article very useful info on rounding.
Now to my query is there a formula that will convert negative figures into positive figures with out affecting the value.
The reason for this is I'm a bookkeeper and when i am downloading reports to excel to match to data downloaded from a CV file from bank. The debit side will sometimes show in a negative dependant on program its downloaded from, its makes it a nuisance when it in a negative as I have to add the figures together as one report will have positive figures and other report will have negative figure. When I would rather just do the usual (this-this=this), the outcome its to have the amount it equals to is 0. Then we know our reports match, & if it doesn't it then helps us find the out of balance amount.
If we add further information to get a percentage or divided value, if the amount is in negative it gives an incorrect figure. Going through every figure and removing the - from the each amount takes up a lot of time, especially when the report is very long. This is time wasted when i could be making use of my time more efficiently.
Im hoping yo will say yep here is an easy fix and not say no sorry no solution. My fingers are crossed
Regards Vivien Anderson
C/- VLA Bookkeeping Services
=(G54-F54)/(K54/L54)*2 = 11013.1765 round