I'm in formula limbo - Help!

[Beauford]

I have been going cray trying to figure this out with no luck. What I need is some kind of formula that will do the following. Take the contents of the following cells.

A1 B1 C1
15 28 19

What I need to do is divide the total of the above by 60 and then round to the nearest half.

So the above is 65 / 60 = 1.08

I need the above to be 1.00

1.35 would be rounded to 1.50

Just so you can understand what the numbers are. The contents in the cells are kilometers and the total of these is divided by 60 to represent minutes. The person I am doing this for gets paid for the time it takes him to travel from one site to another - so if he drives 22km he gets paid for 22 minutes rounded to the closest half hour. So for 22 minutes he gets paid for 1/2 an hour - 44 minutes would be an 1/2 hour and 46 would be 1 hour. The Km's will always be whole numbers - there will never be 22.3km.

I hope this makes sense, 'cause I'm totally confused.

Thanks

B

[CLR]

=MROUND(SUM(A1:C1)/60,0.5)

Vaya con Dios,
Chuck, CABGx3



"Beauford" <[email protected]> wrote in
message news:[email protected]...
>
> I have been going cray trying to figure this out with no luck. What I
> need is some kind of formula that will do the following. Take the
> contents of the following cells.
>
> A1 B1 C1
> 15 28 19
>
> What I need to do is divide the total of the above by 60 and then round
> to the nearest half.
>
> So the above is 65 / 60 = 1.08
>
> I need the above to be 1.00
>
> 1.35 would be rounded to 1.50
>
> Just so you can understand what the numbers are. The contents in the
> cells are kilometers and the total of these is divided by 60 to
> represent minutes. The person I am doing this for gets paid for the
> time it takes him to travel from one site to another - so if he drives
> 22km he gets paid for 22 minutes rounded to the closest half hour. So
> for 22 minutes he gets paid for 1/2 an hour - 44 minutes would be an
> 1/2 hour and 46 would be 1 hour. The Km's will always be whole numbers
> - there will never be 22.3km.
>
> I hope this makes sense, 'cause I'm totally confused.
>
> Thanks
>
> B
>
>
> --
> Beauford
> ------------------------------------------------------------------------
> Beauford's Profile:
http://www.excelforum.com/member.php...o&userid=35309
> View this thread: http://www.excelforum.com/showthread...hreadid=551260
>

[Beauford]

I tried the mround once and got the ##NAME error, but after your post I did some more searching and found I had to add in the mround function. That seems to have done though. Did I mention I hate Office....

5 stars to you.....

Thanks

[CLR]

Sorry Beauford.........my bad for not mentioning that the Analysis ToolPak
had to be installed. I always put it in any setup I'm using first thing,
and once it's there, I just forget about it. Glad you were able to get it
working tho, and thanks for the feedback..........

Vaya con Dios,
Chuck, CABGx3



"Beauford" <[email protected]> wrote in
message news:[email protected]...
>
> I tried the mround once and got the ##NAME error, but after your post I
> did some more searching and found I had to add in the mround function.
> That seems to have done though. Did I mention I hate Office....
>
> 5 stars to you.....
>
> Thanks
>
>
> --
> Beauford
> ------------------------------------------------------------------------
> Beauford's Profile:
http://www.excelforum.com/member.php...o&userid=35309
> View this thread: http://www.excelforum.com/showthread...hreadid=551260
>