MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 don’t want to round to 7.2 until 7 hours and 10 minutes. In other words, I don’t 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 don’t 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 don’t 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