+ Reply to Thread
Results 1 to 5 of 5

Rounding to nearest integer

  1. #1
    pattyh
    Guest

    Rounding to nearest integer

    I have searched many many places and still cannot find my answer. I have a
    worksheet that I am calculating workers minutes allowed - I used
    =SUM(B322:B323) which is correct, then I have to total hours per day - I need
    to total using the number obtained from Min Allowed divided by 60 then
    calulated to the nearest half
    ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8

    I need the formula for rounding to the nearest half hour

    Any help is much appreciated as I'm really lost

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    Perhaps you could use something like this:

    =CEILING(A1,0.5)

    Where A1 contains the value you want to round and rounds it up to the next multiple of 0.5.

    Does that work for you?

    Regards,
    Ron

  3. #3
    N Harkawat
    Guest

    Re: Rounding to nearest integer

    =ceiling(your number ,0.5)

    "pattyh" <[email protected]> wrote in message
    news:[email protected]...
    >I have searched many many places and still cannot find my answer. I have a
    > worksheet that I am calculating workers minutes allowed - I used
    > =SUM(B322:B323) which is correct, then I have to total hours per day - I
    > need
    > to total using the number obtained from Min Allowed divided by 60 then
    > calulated to the nearest half
    > ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8
    >
    > I need the formula for rounding to the nearest half hour
    >
    > Any help is much appreciated as I'm really lost




  4. #4
    Bernard Liengme
    Guest

    Re: Rounding to nearest integer

    To round to units of, say, 4 we can use =ROUND(A1/4,0)*4
    So you could use =ROUND(B1/TIME(0,30,0),0)*TIME(0,30,0) if B1 has to total
    time OR
    =ROUND(SUM(range_with_times)/TIME(0,30,0),0)*TIME(0,30,0) to replace your
    SUM formula
    best wishes
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "pattyh" <[email protected]> wrote in message
    news:[email protected]...
    >I have searched many many places and still cannot find my answer. I have a
    > worksheet that I am calculating workers minutes allowed - I used
    > =SUM(B322:B323) which is correct, then I have to total hours per day - I
    > need
    > to total using the number obtained from Min Allowed divided by 60 then
    > calulated to the nearest half
    > ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8
    >
    > I need the formula for rounding to the nearest half hour
    >
    > Any help is much appreciated as I'm really lost




  5. #5
    Bernard Liengme
    Guest

    Re: Rounding to nearest integer

    Also =ROUND(B2*48,0)/48
    This works since time is a fraction of a day (24 hours), so 48 gives
    fractions of half-hours. Hope you see what I'm getting at!
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "pattyh" <[email protected]> wrote in message
    news:[email protected]...
    >I have searched many many places and still cannot find my answer. I have a
    > worksheet that I am calculating workers minutes allowed - I used
    > =SUM(B322:B323) which is correct, then I have to total hours per day - I
    > need
    > to total using the number obtained from Min Allowed divided by 60 then
    > calulated to the nearest half
    > ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8
    >
    > I need the formula for rounding to the nearest half hour
    >
    > Any help is much appreciated as I'm really lost




+ 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