Excel Function of the Week - ROUND

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

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.

image

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.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Anonymous's picture

is there any function in

is there any function in excel mroundup(), ie to round to a multiple on higher side

jethro's picture

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

Divjey's picture

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

jethro's picture

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

venkatesan's picture

Why 830.70's next multiple of

Why 830.70's next multiple of 10 is 830 and not 840

Zoran's picture

School has taught me it

School has taught me it should be 840.The investor's goal in Forex trading is to profit from foreign currency ... (also called an open position) is a trade in which a trader has bought or sold at a profit.forex traderForex Trading Course, become a successful FOREX trader. A proper FOREX education is your way to profitable foreign currency trading.

Anonymous's picture

Try =Roundup(A1;-1)

Try
=Roundup(A1;-1)

heimann's picture

almost read all post related

almost read all post related Excel because Excel is my favorite subject.
I'm bed pardon your round explanation.

Carbon CopyPro's picture

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

power wheels jeep's picture

It such a good informative

It such a good informative article

Anonymous's picture

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

jethro's picture

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

jethro's picture

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)))

Anon's picture

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.

jethro's picture

=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