http://office.microsoft.co

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

http://wiki.services.openo

Hope this helps!

Solved

Posted on 2011-10-08

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.

(1) (a)

(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

Thanks.

16 Comments

http://office.microsoft.co

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

http://wiki.services.openo

Hope this helps!

=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

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

10 --> 10

11 --> 10

12 --> 10

13 --> 10

14 --> 10

15 --> 20

16 --> 20

17 --> 20

18 --> 20

19 --> 20

20 --> 20

cheers, teylyn

=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(numbe

regards, barry

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

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.

>>Please, no more comments for now.

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

Saqib

Thank you all for your patience and advice.

Title | # Comments | Views | Activity |
---|---|---|---|

How to add prompts to user defined function? | 6 | 27 | |

excel VBA sending mail | 9 | 10 | |

VBA Help TT V-1.1 | 15 | 24 | |

VBA Object does not support property or method | 3 | 21 |

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

Connect with top rated Experts

**15** Experts available now in Live!