Rounding question
Posted by Alan Holle on August 23, 2001 2:17 PM
I am dealing with a timekeeping operation that only uses tenths. I need to change the way excel rounds. For example 12:00 p.m. to 7:09 p.m. is 7 hours and 9 minutes. When I convert this to a decimal, excel rounds to 7.2. I dont want to round to 7.2 until 7 hours and 10 minutes. In other words, I dont want to round up to the next tenth until the fourth minute (e.g., 1.5 should round to 1 and 1.6 should round to 2). Any suggestions?
Posted by Barrie Davidson on August 23, 2001 2:40 PM
Assuming your formula that returns 7.2 when rounded to one decimal place is in cell A1, try this formula
=MROUND(A1,0.1)
Hope this helps you out.
Barrie
Posted by Aladin Akyurek on August 23, 2001 2:45 PM
=IF((A1-INT(A1))<=0.5,FLOOR(A1,1),CEILING(A1,1))
Posted by Mark W. on August 23, 2001 2:50 PM
=FLOOR(("7:09 P"-"12:00 P")*24,0.1)
Posted by R De Leon on August 23, 2001 2:54 PM
Posted by Mark W. on August 23, 2001 3:01 PM
BTW...
> I dont want to round to 7.2 until 7 hours and
> 10 minutes.
I believe you meant to say until 7 hrs and 12 min.
=12/60 is 0.2
Posted by Mark W. on August 23, 2001 3:07 PM
And...
> I dont want to round up to the next tenth
> until the fourth minute
Actually, it would be multiples of six minutes...
{6,12,18,24,30,36,42,48,54,60}
Posted by Mark W. on August 23, 2001 3:12 PM
Barrie, this will roundup to 7.2 at 7:11 P (or
7.18333333333333) which is 1 minute too soon.
Posted by Barrie Davidson on August 23, 2001 6:14 PM
Good catch Mark...
I guess I should have spent a little more time reviewing my proposed solution. Doesn't pay to be too quick sometimes!
Thanks Mark :)
Barrie
