?
Solved

Round to the nearest 10 in Excel or Open Office Calc

Posted on 2011-10-08
17
Medium Priority
?
1,008 Views
Last Modified: 2013-08-06
Is there a function, or does somebody have a formula or way of attacking these 3 problems, all in MS Excel (could also give an answer using Open Office Calc):  

(1) (a) How do you round to the nearest 10?  (b) Better yet, express the answer more generally.  So, the question, ideally, is, "How do you round to the nearest x, where x = a given, real number?"

(2) (a) How do you round up to the nearest 10?  (b) Same follow-up as in question (1).

(3) (a) How do you round down to the nearest 10?  (b) Same follow-up as in question (1).

I have Excel 2003.  I'd prefer an answer in Excel to one requiring Open Office Calc, but if there's a much easier way to do it in the latter, I'd be very interested in knowing that fact.  It will probably be easiest to follow this thread if people specify which problem they're addressing, for example, "Problem (2b)".

Thanks.
0
Comment
  • 5
  • 3
  • 3
  • +4
16 Comments
 
LVL 9

Assisted Solution

by:pritamdutt
pritamdutt earned 400 total points
ID: 36937797
Please see the following link for details on Round, RoundUp and RoundDown Functions of MS Excel

http://office.microsoft.com/en-us/excel-help/round-a-number-HP003056144.aspx#_Toc272405096

Also see the following link for reference on Round, RoundUp and RoundDown Functions of OpenOffice Calc

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_ROUNDUP_function


Hope this helps!
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 400 total points
ID: 36937990
Hello,

=CEILING(A1,10)

will round a number UP to the nearest multiple of 10, for example

15      -->      20
11      -->      20
17      -->      20


To round down, you can use
=FLOOR(A1,10)

which will round a number DOWN to the nearest multiple of 10, for example

=FLOOR(A1,10)

15      -->      10
11      -->      10
17      -->      10

cheers, teylyn
0
 
LVL 50
ID: 36938003
You can use MOD() in an IF statement to decide whether to round up or down

=IF(MOD(A1/10,1)>=0.5,CEILING(A1,10),FLOOR(A1,10))


10       -->      10
11       -->      10
12       -->      10
13       -->      10
14       -->      10
15       -->      20
16       -->      20
17       -->      20
18       -->      20
19       -->      20
20       -->      20


cheers, teylyn
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 400 total points
ID: 36938379
If the number to round to is divisible by 10 (or 1 divided by a multiple of 10, like 0.1, 0.01, etc.) then you can use ROUND/ROUNDUP/ROUNDDOWN as already suggested. But if not then MROUND function allows you to round to the nearest n

=MROUND(number,n)

Note that MROUND is an "Analysis ToolPak" add-in function so that needs to be enabled in Excel 2003 (in later versions MROUND is a built-in function). To enable Analysis ToolPak use Data > addins > tick "Analysis ToolPak"

To avoid using MROUND this is a generic way to round to n using ROUND function,

=ROUND(number/n,0)*n

so to round to the nearest 4 that would be

=ROUND(number/4,0)*4

That exact same approach can be used with ROUNDUP and ROUNDDOWN to round up or down to the nearest n. CEILING and FLOOR are generally simpler, as per teylyn's suggestion, but note that if you want to round up negative and positive numbers you need to make sure that the "significance" argument has the same sign as the number to be rounded. YOu can do that with this formula

=CEILING(number,SIGN(number)*4)

regards, barry
0
 

Author Comment

by:therearestupidquestions
ID: 36951581
Whoops!  Sorry.  I forgot I had this question open.  Let me try to review this stuff today.  I hope I have time to get to it.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 400 total points
ID: 36970830
Or maybe

Round
=INT((number+x/2)/x)*x

Round up
=INT((number+x)/x)*x

Round down
=INT(number/x)*x
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36971523
=INT((number+x)/x)*x

Hello Saqib,

I don't think that will work if number is a multiple of x, e.g. if number is 18 and x is 6 you'd expect a roundup function to return 18...but that would return 24.....

regards, barry
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36971572
Thanks, Barry, I did not realize that.

Maybe we can modify it to

=INT(($F2+G$1*0.99999999)/G$1)*G$1

0
 

Author Comment

by:therearestupidquestions
ID: 37015989
Please, no more comments for now.

I'm raising the point value to reward experts for their patience.

I'll review answers and award points ASAP.  I may have to post another post along these lines, before that time, to keep the question active.  However, I do appreciate people's responses, and nobody will be cheated out of points they deserve.  You just may have to wait a bit.

Sorry about this.
0
 

Author Comment

by:therearestupidquestions
ID: 37061755
I bumped up the points to reward ongoing patience.

Thanks for waiting.
0
 

Author Comment

by:therearestupidquestions
ID: 37105452
Again, I bumped up the points.

Thanks, all.
0
 
LVL 50
ID: 37105767
You said above:

>>Please, no more comments for now.

So, did you review the options presented? What is your result?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37105775
Hi teylyn, I think the OP is not yet ready to review the options. "No more comments..." could be because EE stalls the asker's ability to ask more questions if the experts' comments are waiting long enough.

Saqib
0
 

Author Comment

by:therearestupidquestions
ID: 37276987
I will award points in the next 2 days.  I'm currently printing all the threads I have going, even as I write this.  I'll award 500 points total.

Thank you all for your patience and advice.
0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 38560868
Hello, I just read the posts that you probably have an answer, but I think this is a readable solution:



=IF(RIGHT(A2, 1)+0>=5, CEILING(A2, 10), FLOOR(A2, 10))
0
 
LVL 24

Expert Comment

by:Tracy
ID: 39385212
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question