Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding numbers up or down | Excel Discussion (Misc queries) | |||
Rounding to the nearest 9th | Excel Discussion (Misc queries) | |||
Rounding numbers to the nearest thousand | Excel Discussion (Misc queries) | |||
Rounding number to the nearest quarter | Excel Worksheet Functions | |||
Rounding a number down to the nearest 5? | Excel Worksheet Functions |