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

 

[Guest]

check MROUND

 

[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]...

 

[Ron Rosenfeld]

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