+ Reply to Thread
Results 1 to 9 of 9

formulae : Rounding question

  1. #1
    Registered User
    Join Date
    11-27-2008
    Location
    Norn Ireland
    Posts
    4

    formulae : Rounding question

    Howdie, I have checked the search but to be hoenst it dont make that much sence to me.

    Bit of a beginner at this, I have created the start of a lab worksheet for uni, I want to play with it to make sure I understand the principles before exam time.

    I need to round some formulae to the closest 10 (ie 2445 changes to 2450 and 2444 changes to 2440)

    Also need to round different series to closest 0.2


    I have used the mround command but it adds the original figure- when I then subtract answer if the original figure changes then the formulae is wrong.

    Thanks folks.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    Hi,

    =MROUND(A1,10)

    rounds 2445 to 2450 and 2444 to 2440 for me. Is this not what you want?
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi, and welcome to the forum.

    Just use

    =ROUND(your number,-1)

    HTH

  4. #4
    Registered User
    Join Date
    11-27-2008
    Location
    Norn Ireland
    Posts
    4
    Quote Originally Posted by Richard Buttrey View Post
    Hi, and welcome to the forum.

    Just use

    =ROUND(your number,-1)

    HTH

    Ok I am getting there slowly -

    if I have the formulae =(G7/1000)/O4 and I want that cell to be rounded, without showing the correct answer and having another cell with the rounding on it how do I do it?


    BTW thanks both for the super speedy reply.

  5. #5
    Registered User
    Join Date
    11-27-2008
    Location
    Norn Ireland
    Posts
    4
    Quote Originally Posted by sweep View Post
    Hi,

    =MROUND(A1,10)

    rounds 2445 to 2450 and 2444 to 2440 for me. Is this not what you want?
    It is, however, I did not explain that I wanted my answer rounded - and I dont want to show the 'correct' answer.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Not sure what mean about the formula being wrong but, in general,

    You can always round to any multiple of 10 using ROUND, e.g. to ROUND to the nearest 10

    =ROUND(A1,-1)

    for other numbers you can use MROUND but some people like to avoid MROUND because it's an Analysis ToolPak add-in function (unless you have Excel 2007) so as a work around, you can round to the nearest n by using

    =ROUND(A1/n,0)*n

    so to round to the nearest 0.2

    =ROUND(A1/0.2,0)*0.2

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Not quite sure what you mean by 'without showing the correct answer'. However assuming your formula above is in A1 and is unrounded, in B1 enter

    =Round(A1,-1)

    or

    sweep's

    =MROUND(A1,10)

    HTH

  8. #8
    Registered User
    Join Date
    11-27-2008
    Location
    Norn Ireland
    Posts
    4
    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Not quite sure what you mean by 'without showing the correct answer'. However assuming your formula above is in A1 and is unrounded, in B1 enter

    From the replies it may not be possible - I wanted to have the formulae =(G7/1000)/O4 in H7 AND I wanted H7 ALSO rounded to the nearest 10.

    Thus H7 correct answer is 2415 but I want it to show 2420.

    Hope that clarifies it.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Just replace A1 in any of the formulas given with (G7/1000)/O4
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1