Don’t be frightened I am not giving you any fifth graders’ challenge. But in real life you might be facing similar situation where you have a stack of numbers that require to be rounded either to nearest dollar or nearest 10 or 100.
So how would you do it in Excel? Well you don’t really have to bite your nails out to get this job done. Excel gives you not ONE not TWO but THREE functions to get around rounding named ROUND, ROUNDUP and ROUNDDOWN.
Syntax of all three is similar and as the names suggest ROUND function rounds as normal, ROUNDUP function rounds the number up whereas ROUNDDOWN rounds the number down.
ROUND = (number,num_digits)
ROUNDUP = (number,num_digits)
ROUNDDOWN = (number,num_digits)
Whereas;
number: the number you want to round which can be a number or a cell reference containing a number.
num_digits: the number of digits or places you want Excel to round the number which can be a positive number, a zero or a negative number.
Yes you read it right. Many don’t know that you can use negative numbers as num_digits argument.
In short,
- Positive numbers will affect the digits at the right of the decimal. For example if you put “2” then it means you want the number to be rounded to 2 decimal places
- Zero will ask the Excel to shed of all the decimal numbers and round the number to nearest whole number
- Negative numbers on the other hand will affect the numbers at the left of the decimal. For example if you use -1 then it means you want the number to be rounded to nearest 10.
Say you have following numbers: 56, 18, 19.2, 2.56, 1.8, 28.9, 554578.2212
And your rounding requirement can be either of these:
- Round, round up/down to 2 decimal places
- Round, round up/down to 1 decimal place
- Round, round up/down to nearest whole number
- Round, round up/down to nearest 10
- Round, round up/down to nearest 100
To quickly understand and compare the output of all three functions I have arranged the data as follows:
I put the formulas as follows:
Cell B2: =ROUND(A2,$B$10)
Cell C2: =ROUNDUP(A2,$B$10)
Cell D2: =ROUNDDOWN(A2,$B$10)
Important: I used cell B10 as a num_digit argument so that I can change it to show it affects the rounding function.
Now in cell B10 you can type in the number to make the rounding as you desire. Remember the number you use will affect the rounding process and nature:
Important: If nothing is mentioned in the cell B10 then Excel will conduct the calculation by assuming “0” as the argument. But while writing the formula directly you have to provide something to make the formula work.
So here you have an extra dose on Excel and Rounding together. Just remember that you can use negative numbers in ROUND functions and it gets you nearest of 10 or 100 or 1000 or whatever you fancy.
good morning/afternoon can you help me to have a formula.
(491.10)
to look like this ? Four Hundred Ninety Four and 10/100
Typo
TRUNC(formula,1) should be TRUNC(formula,0)
You could have mentioned that a % is actually a 2 place decimal number so rounding to .1% needs a -3 as the level,
also there is the difference between excel function, and VBA in the handling of the rounding of 1/2 a ‘unit’
and the effect of floating point on numbers held in Excel when considering something like $1.005 or even $1.10 – and the need for all calculated values of cash to be handled with appropriate regard to the bankable amounts, followed by the consideration of trying to program for something like a 3 way split of something like $100.00 – round to nearest leaves the sum of the splits $0.01 short of a start value of $100, and $0.01 over a start value of $200.00
Maybe the functions ABS() MOD(,) CEILING.MATH(,) FLOOR.MATH(,) MROUND(,) and TRUNC(,) as well as INT() should be considered for the next post ?
Karolina – round the (7875/2000) – should get you an integer of 4 that you will multiply by the 250
note INT(formula) is effectively TRUNC(formula,1) or ROUNDDOWN(formula,0) to get the whole number part of the formula result.
Ramesh – For a specific answer you need to reword the question more clearly
next number after 15 is ???? and 15 divided by 10 is 1.5
if by next number you mean (15+1) then that divided by 10 is 1.6 and.. that gets you problems with excel storing the result as a floating point value using negative powers of 2
1.5 is 1 + 1/2
1.6 is 1 + 1/2 +0/4 +0/8 + 1/16 ….. etc. so Excel stores an approximation of the true answer .
good
Thank you so much.
THIS IS VERY HELPFUL
i have an amount 15 and we want next number divided by 10
15 next complete by 10 what in excel formula.
Thank you. Great explanation. I love that formula.
I have an amount ex. 7,875. In another box I need to calculate how many 250 goes into it for every 2000 so I did =(7875/2000)*250 now it give me 984 and I need it to round it to 1000. Is there a function for that?