Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Avoiding Value error message due to space in cell w/ formula
I have a time sheet in Excel that will be used by nearly 1000 employees. I'm
trying to make it both user friendly and foolproof. Currently, I have cells for time in and out and then a cell that calculates the time worked. If someone clears the "In" or "Out" cell with the space bar, the formula cell returns a Value error message. How can I avoid this? Here's my formula: =ROUND((SUM((((C16-B16)+(B16C16))*1440)/60)+(((E16-D16)+(D16E16))*1440/60))/0.25,0)*0.25 Thanks for any help correcting this or if you have suggestions regarding the time formula I am very grateful. A.R. Hunt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Avoiding Value error message due to space in cell w/ formula
A.R. Hunt <A.R. wrote...
I have a time sheet in Excel that will be used by nearly 1000 employees. I'm trying to make it both user friendly and foolproof. Currently, I have cells for time in and out and then a cell that calculates the time worked. If someone clears the "In" or "Out" cell with the space bar, the formula cell returns a Value error message. How can I avoid this? Here's my formula: =ROUND((SUM((((C16-B16)+(B16C16))*1440)/60) +(((E16-D16)+(D16E16))*1440/60))/0.25,0)*0.25 .... So you're rounding to quarter hours? Easier to multiply by 96 (24*4) than by 1440*60/0.25, 96 being no more obscure than 1440. Eliminate the unnecessary SUM call and parentheses. Try =ROUND((N(C16)-N(B16)+(B16C16)+N(E16)-N(D16)+(D16E16))*96,0)/4 N(.) returns 0 if its argument evaluates to text, FALSE or refers to a blank cell. It'll only return nonzero numbers when its argument evaluates to a number or TRUE, in which case it returns 1. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Avoiding Value error message due to space in cell w/ formula
Hi,
One way: =IF(ISERROR(ROUND((SUM((((C16-B16)+(B16C16))*1440)/60)+(((E16-D16)+(D16E16))*1440/60))/0.25,0)*0.25 ),"",ROUND((SUM((((C16-B16)+(B16C16))*1440)/60)+(((E16-D16)+(D16E16))*1440/60))/0.25,0)*0.25 ) HTH Jean-Guy "A.R. Hunt" wrote: I have a time sheet in Excel that will be used by nearly 1000 employees. I'm trying to make it both user friendly and foolproof. Currently, I have cells for time in and out and then a cell that calculates the time worked. If someone clears the "In" or "Out" cell with the space bar, the formula cell returns a Value error message. How can I avoid this? Here's my formula: =ROUND((SUM((((C16-B16)+(B16C16))*1440)/60)+(((E16-D16)+(D16E16))*1440/60))/0.25,0)*0.25 Thanks for any help correcting this or if you have suggestions regarding the time formula I am very grateful. A.R. Hunt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Avoiding Value error message due to space in cell w/ formula
Thanks! This seems to have fixed my problem. Thanks also for helping with the
time formula. I'd modified a formula from another thread here and was afraid to make changes. Since I wasn't sure I understood all the arguments, I figured I better use it pretty much as it was. "Harlan Grove" wrote: A.R. Hunt <A.R. wrote... I have a time sheet in Excel that will be used by nearly 1000 employees. I'm trying to make it both user friendly and foolproof. Currently, I have cells for time in and out and then a cell that calculates the time worked. If someone clears the "In" or "Out" cell with the space bar, the formula cell returns a Value error message. How can I avoid this? Here's my formula: =ROUND((SUM((((C16-B16)+(B16C16))*1440)/60) +(((E16-D16)+(D16E16))*1440/60))/0.25,0)*0.25 .... So you're rounding to quarter hours? Easier to multiply by 96 (24*4) than by 1440*60/0.25, 96 being no more obscure than 1440. Eliminate the unnecessary SUM call and parentheses. Try =ROUND((N(C16)-N(B16)+(B16C16)+N(E16)-N(D16)+(D16E16))*96,0)/4 N(.) returns 0 if its argument evaluates to text, FALSE or refers to a blank cell. It'll only return nonzero numbers when its argument evaluates to a number or TRUE, in which case it returns 1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Text in formula bar is not displaying in cell | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions |