Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
pattyh
 
Posts: n/a
Default Rounding to nearest integer

I have searched many many places and still cannot find my answer. I have a
worksheet that I am calculating workers minutes allowed - I used
=SUM(B322:B323) which is correct, then I have to total hours per day - I need
to total using the number obtained from Min Allowed divided by 60 then
calulated to the nearest half
ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8

I need the formula for rounding to the nearest half hour

Any help is much appreciated as I'm really lost
  #2   Report Post  
N Harkawat
 
Posts: n/a
Default

=ceiling(your number ,0.5)

"pattyh" wrote in message
...
I have searched many many places and still cannot find my answer. I have a
worksheet that I am calculating workers minutes allowed - I used
=SUM(B322:B323) which is correct, then I have to total hours per day - I
need
to total using the number obtained from Min Allowed divided by 60 then
calulated to the nearest half
ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8

I need the formula for rounding to the nearest half hour

Any help is much appreciated as I'm really lost



  #3   Report Post  
Bernard Liengme
 
Posts: n/a
Default

To round to units of, say, 4 we can use =ROUND(A1/4,0)*4
So you could use =ROUND(B1/TIME(0,30,0),0)*TIME(0,30,0) if B1 has to total
time OR
=ROUND(SUM(range_with_times)/TIME(0,30,0),0)*TIME(0,30,0) to replace your
SUM formula
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pattyh" wrote in message
...
I have searched many many places and still cannot find my answer. I have a
worksheet that I am calculating workers minutes allowed - I used
=SUM(B322:B323) which is correct, then I have to total hours per day - I
need
to total using the number obtained from Min Allowed divided by 60 then
calulated to the nearest half
ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8

I need the formula for rounding to the nearest half hour

Any help is much appreciated as I'm really lost



  #4   Report Post  
Bernard Liengme
 
Posts: n/a
Default

Also =ROUND(B2*48,0)/48
This works since time is a fraction of a day (24 hours), so 48 gives
fractions of half-hours. Hope you see what I'm getting at!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pattyh" wrote in message
...
I have searched many many places and still cannot find my answer. I have a
worksheet that I am calculating workers minutes allowed - I used
=SUM(B322:B323) which is correct, then I have to total hours per day - I
need
to total using the number obtained from Min Allowed divided by 60 then
calulated to the nearest half
ex: 7.1, 7.2, 7.3,7.4 = 7.5 and/or 7.6, 7.7, 7.8, 7.9 = 8

I need the formula for rounding to the nearest half hour

Any help is much appreciated as I'm really lost



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding numbers up or down boyshanks Excel Discussion (Misc queries) 13 April 8th 09 03:03 PM
Rounding to the nearest 9th Corby Excel Discussion (Misc queries) 16 November 13th 07 11:28 AM
Rounding numbers to the nearest thousand Mark Excel Discussion (Misc queries) 2 March 10th 05 01:13 PM
Rounding number to the nearest quarter Dajana Excel Worksheet Functions 1 February 17th 05 03:21 PM
Rounding a number down to the nearest 5? maluli Excel Worksheet Functions 3 January 14th 05 07:23 PM


All times are GMT +1. The time now is 06:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"