Something like CEILING or FLOOR

[gusvenables]

Hi,

I am currently working on a project whereby I enter fractions of hours, and they appear in cells as decimals. So, for example, if I want to enter 10 minutes, I enter 10/60 and the value in the cell comes up as 0.1666666.... (I round it to 2 decimal places, though!)

Okay, so that's the easy part. The next part of the problem comes if I wish to "round" (or whatever the correct term is) the number to the nearest quarter hour. So, for example, if I've entered 10 minutes (or 0.1666666....) I need to round it UP to 15 minutes, or 0.25. If I've entered 20 minutes (or 0.333333....), I need to round it DOWN to 15 minutes, or 0.25.

(Sorry if I'm being ponderous in my spelling of things out; I'm sure most of you probably get the point of what I'm trying to say!)

Now, I know about the CEILING and FLOOR functions, but what I'm looking for here is something that really combines the two, or selects from these two the most appropriate function. So, in other words, it's something similar to ROUND, in that it "rounds" either up or down, but works in the same way as CEILING or FLOOR. Does that make sense? Is it possible for Excel to do that?

Gus Venables

[JMB]

check MROUND


"gusvenables" wrote:

>
> Hi,
>
> I am currently working on a project whereby I enter fractions of hours,
> and they appear in cells as decimals. So, for example, if I want to
> enter 10 minutes, I enter 10/60 and the value in the cell comes up as
> 0.1666666.... (I round it to 2 decimal places, though!)
>
> Okay, so that's the easy part. The next part of the problem comes if I
> wish to "round" (or whatever the correct term is) the number to the
> nearest quarter hour. So, for example, if I've entered 10 minutes (or
> 0.1666666....) I need to round it UP to 15 minutes, or 0.25. If I've
> entered 20 minutes (or 0.333333....), I need to round it DOWN to 15
> minutes, or 0.25.
>
> (Sorry if I'm being ponderous in my spelling of things out; I'm sure
> most of you probably get the point of what I'm trying to say!)
>
> Now, I know about the CEILING and FLOOR functions, but what I'm looking
> for here is something that really combines the two, or selects from
> these two the most appropriate function. So, in other words, it's
> something similar to ROUND, in that it "rounds" either up or down, but
> works in the same way as CEILING or FLOOR. Does that make sense? Is it
> possible for Excel to do that?
>
> Gus Venables
>
>
> --
> gusvenables
> ------------------------------------------------------------------------
> gusvenables's Profile: http://www.excelforum.com/member.php...o&userid=28408
> View this thread: http://www.excelforum.com/showthread...hreadid=480009
>
>

[Peo Sjoblom]

if you follow excel time com and if you enter =10/60 you'll get 4 hours not
10 minutes, ten minutes would be =10/1440

if you want to use time and round to the nearest 15 minute

=ROUND(C1*96,0)/96

will round 00:10 to 00:15 where C1 holds the time
if you want decimals just multiply the lot with 24

=(ROUND(C1*96,0)/96)*24

format as general or number

could be written

=(ROUND((10/1440)*96,0)/96)*24

If you still insist to enter 10/60 then you must use

=ROUND((C1/24)*96,0)/96


--
Regards,

Peo Sjoblom

(No private emails please)


"gusvenables" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I am currently working on a project whereby I enter fractions of hours,
> and they appear in cells as decimals. So, for example, if I want to
> enter 10 minutes, I enter 10/60 and the value in the cell comes up as
> 0.1666666.... (I round it to 2 decimal places, though!)
>
> Okay, so that's the easy part. The next part of the problem comes if I
> wish to "round" (or whatever the correct term is) the number to the
> nearest quarter hour. So, for example, if I've entered 10 minutes (or
> 0.1666666....) I need to round it UP to 15 minutes, or 0.25. If I've
> entered 20 minutes (or 0.333333....), I need to round it DOWN to 15
> minutes, or 0.25.
>
> (Sorry if I'm being ponderous in my spelling of things out; I'm sure
> most of you probably get the point of what I'm trying to say!)
>
> Now, I know about the CEILING and FLOOR functions, but what I'm looking
> for here is something that really combines the two, or selects from
> these two the most appropriate function. So, in other words, it's
> something similar to ROUND, in that it "rounds" either up or down, but
> works in the same way as CEILING or FLOOR. Does that make sense? Is it
> possible for Excel to do that?
>
> Gus Venables
>
>
> --
> gusvenables
> ------------------------------------------------------------------------
> gusvenables's Profile:
> http://www.excelforum.com/member.php...o&userid=28408
> View this thread: http://www.excelforum.com/showthread...hreadid=480009
>

[Ron Rosenfeld]

On Thu, 27 Oct 2005 20:51:09 -0500, gusvenables
<[email protected]> wrote:

>
>Hi,
>
>I am currently working on a project whereby I enter fractions of hours,
>and they appear in cells as decimals. So, for example, if I want to
>enter 10 minutes, I enter 10/60 and the value in the cell comes up as
>0.1666666.... (I round it to 2 decimal places, though!)
>
>Okay, so that's the easy part. The next part of the problem comes if I
>wish to "round" (or whatever the correct term is) the number to the
>nearest quarter hour. So, for example, if I've entered 10 minutes (or
>0.1666666....) I need to round it UP to 15 minutes, or 0.25. If I've
>entered 20 minutes (or 0.333333....), I need to round it DOWN to 15
>minutes, or 0.25.
>
>(Sorry if I'm being ponderous in my spelling of things out; I'm sure
>most of you probably get the point of what I'm trying to say!)
>
>Now, I know about the CEILING and FLOOR functions, but what I'm looking
>for here is something that really combines the two, or selects from
>these two the most appropriate function. So, in other words, it's
>something similar to ROUND, in that it "rounds" either up or down, but
>works in the same way as CEILING or FLOOR. Does that make sense? Is it
>possible for Excel to do that?
>
>Gus Venables

To round to the nearest 0.25:

=ROUND(ur_nbr/0.25,0)*0.25


--ron