Excel Function of the Week - ROUND
The round function enables you to truncate values to the number of decimal places you desire. This could be in fractions of a whole value e.g. hundredths and thousandths of whole number, or as large numbers, e.g. rounding to the closest million or billion.
There are some very similar functions, ROUNDUP, ROUNDDOWN and MROUND. In addition there are some complimentary functions such as FLOOR and CEILING.
These functions all perform the same way with specific variations. Round follows the 4/5 round principle. That is if the number ends in 4 or lower it rounds down, and if it ends in 5 or higher it rounds up.
The syntax is to have the number followed being rounded (or a link to it) followed by a number that represents the way you want to round the number. The number of digits is expressed as a positive value for decimal places (numbers between 1 or –1 and zero) and a negative number for values greater than zero.
Specifically it should be zero to round to a whole number, and a negative number to round into tens, hundreds, thousands etc. The way to work it out is to think how many numbers either side of the decimal point you want to round to. Negative numbers are to the left, and positive numbers to the right.
Here are some examples:
- If you want to truncate long calculated decimal place values back to 2 places try =ROUND(A1,2)
- If you want to round a value to a whole number try =ROUND(A1,0)
- To round a number to the closest 10,000, use =ROUND(A1,-4)
Some specific notes from Excel Help.
- If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
- If num_digits is 0, the number is rounded to the nearest integer.
- If num_digits is less than 0, the number is rounded to the left of the decimal point.
- To always round up (away from zero), use the ROUNDUP function.
- To always round down (toward zero), use the ROUNDDOWN function.
- To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function.
Site Construction and Maintenance by Jethro Consultants
Gidday
Glad to have you stop in.
I write about my family, the internet, technology and gadgets, productivity with Microsoft Office applications Excel Tips and more.
Instagram Feed
Find Us On...
Subscribe by RSS
Recent comments
- ill try to resolve that
10 years 37 weeks ago - When I initially commented I
10 years 37 weeks ago - What I would really like to
10 years 39 weeks ago - sure @spyjournal
10 years 39 weeks ago - Howdy! Do you use Twitter?
10 years 39 weeks ago - You really make it appear
10 years 39 weeks ago - Oh my goodness! Awesome
10 years 39 weeks ago - I think you have to do it via
10 years 39 weeks ago - When I originally commented I
10 years 39 weeks ago - go to hell india
10 years 39 weeks ago
Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Timothy Miller © unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission. Privacy Policy
Comments
is there any function in
is there any function in excel mroundup(), ie to round to a multiple on higher side
no but this is where you can
no
but this is where you can combine the use of MOD and MROUND
use mod to determine the remainder when dividing by the multiple, and then add the divisor less the remainder to the answer when it rounds down
eg if you want to round up to the nearest 5 cents, for example, use this formula below.
note to calculate this i had my range of values to be checked in column A and my divisor (.05) in cell C1
=IF(MROUND(A1,$C$1)
basically what this formula does is it looks at the result of MROUND. If it returns a value less than the starting point (which it will when rounding down) it then adds the divisor less the remainder (ie the difference needed to get up to the next multiple). If not, it just uses the result of mround
I would like to know the
I would like to know the formula to work out the following calculation in excel worksheet.
The decimal point (two digits) should be rounded off to the next multiple of 10. To illustrate, if the amount comes to 830.70, then the amount will be rounded off to 830, if the amount works out to be 831.40, then will be rounded off to 840.
Thanks & Regards
Hi Divjey assuming the value
Hi Divjey
assuming the value you are wanting to round is in cell A1 then the formula would be
=ROUND(A1,0)
The digit represents the number of decimal places you are rounding to - in this case zero.
Cheers
Why 830.70's next multiple of
Why 830.70's next multiple of 10 is 830 and not 840
School has taught me it
School has taught me it should be 840.
Try =Roundup(A1;-1)
Try
=Roundup(A1;-1)
almost read all post related
almost read all post related Excel because Excel is my favorite subject.
I'm bed pardon your round explanation.
Excel tips and Excel help
Excel tips and Excel help from the MrExcel Message Board regarding ... This formula always returns the Monday of the week in question (you can check .... In A4 enter: =DATE(A2,ROUND(A1/4,0),1)+IF(2
It such a good informative
It such a good informative article
I have huge financial
I have huge financial spreadsheets & want to round a RANGE of cells to the nearest 5.
I can't find a formula for that??
Thanks,
Kim
Hi Kim - you need to combine
Hi Kim - you need to combine several formulas - SUM and ROUND.
Heres an example =ROUND(SUM(A1:A5),2) will round to nearest 1 cent. getting it to nearest 5 cents involves using the MOD function and an IF Statement.
(more on the MOD function here http://www.spyjournal.biz/node/951 )
here is a very complicated formula i made up to do this - i am sure someone with some time can come up with a less complex one
=IF(MOD(MOD(ROUND(SUM(A1:A6),2),1),0.05)<0.02,ROUND(SUM(A1:A6),2)-MOD(MOD(ROUND(SUM(A1:A6),2),1),0.05),ROUND(SUM(A1:A6),2)+(0.05-MOD(MOD(ROUND(SUM(A1:A6),2),1),0.05)))
basically it says if the divisor after rounding to two decimal places is 0,1 or 2 cents then it subtracts it, if its over it adds the difference between 5 cents and the divisor.
cheers
Tim
or if you and i both read the
or if you and i both read the post again - you will note this very simple solution
To round a number to a specific multiple (for example, to round to the nearest 0.5), use the MROUND function.
so =MROUND(SUM(A1:A6),0.05)
also int he above other forumla - should u want to use it - there needs to be an = sign included in the first <
=IF(MOD(MOD(ROUND(SUM(A1:A6),2),1),0.05)<=0.02,ROUND(SUM(A1:A6),2)-MOD(MOD(ROUND(SUM(A1:A6),2),1),0.05),ROUND(SUM(A1:A6),2)+(0.05-MOD(MOD(ROUND(SUM(A1:A6),2),1),0.05)))
I have to calculate the
I have to calculate the percentage of miles driven in each state by our fleet of trucks.
I currently have all the totals and get the percentage of the total. I now have percentage totals that are less then 1 ie. .0007 & .014 How would I round a cell with a total greater then 0.00 but less then 1.00 up to 1.00 and leave any cell that is greater then 1.00 alone.
=IF(OR(A2>=1,A2<0),A2,ROUNDUP
=IF(OR(A2>=1,A2<0),A2,ROUNDUP(A2,0)) - where a2 is the cell with the value in it