Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISERROR with more than 2 cells of data
I have a worksheet with breakdown calculations of hours worked in a given day
for a person (morning block of time, afternoon block of time & possibly a third block of time). I am now attempting to calculate the total number of hours a person worked for a given day. I have used =IF(ISERROR(O2+Q2),"-",O2+Q2) with cells O2 & Q2 being hours worked in a block of time. Some people have a 3rd block of time in a day (column S) and if I try =IF(ISERROR(O2+Q2+S2),"-",O2+Q2+S2) it either gives me "-" or an error, which I know to not be an accurate result. What am I doing wrong? Thank you! -- heyredone |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISERROR with more than 2 cells of data
Hard to tell without knowing how you're calculating O2, Q2 & S2, but you
might want to use SUM(), since it ignores text ('+' will return the #VALUE! error if one of the arguments is text): =SUM(O2,Q2,S2) and format the cell to display - if the result is 0 (e.g., Format/Cells/Number/Custom: General;General:"-";@ In article , heyredone wrote: I have a worksheet with breakdown calculations of hours worked in a given day for a person (morning block of time, afternoon block of time & possibly a third block of time). I am now attempting to calculate the total number of hours a person worked for a given day. I have used =IF(ISERROR(O2+Q2),"-",O2+Q2) with cells O2 & Q2 being hours worked in a block of time. Some people have a 3rd block of time in a day (column S) and if I try =IF(ISERROR(O2+Q2+S2),"-",O2+Q2+S2) it either gives me "-" or an error, which I know to not be an accurate result. What am I doing wrong? Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISERROR with more than 2 cells of data
Your problem is that if any one of your cells has an error (like S2), then
you get the dash. However, in your case if S2 has an error, you still want the total of O2 and Q2. So try it this way: =if(iserror(s2+0),if(iserror(o2+q2),"-",o2+q2),o2+q2+s2) However, this begs the question as to why you are creating an error when the employee has worked zero hours. Why not enter 0 when no hours are worked? Then you can simply sum the hours (o2+q2+s2) without needing to check for errors. If you want a dash displayed when the hours are zero, you're better off to use a custom format like: 0.0;-0.0;- This way you get the display you want, but can still do arithmetic on the cell. Regards, Fred. "heyredone" wrote in message ... I have a worksheet with breakdown calculations of hours worked in a given day for a person (morning block of time, afternoon block of time & possibly a third block of time). I am now attempting to calculate the total number of hours a person worked for a given day. I have used =IF(ISERROR(O2+Q2),"-",O2+Q2) with cells O2 & Q2 being hours worked in a block of time. Some people have a 3rd block of time in a day (column S) and if I try =IF(ISERROR(O2+Q2+S2),"-",O2+Q2+S2) it either gives me "-" or an error, which I know to not be an accurate result. What am I doing wrong? Thank you! -- heyredone |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISERROR with more than 2 cells of data
Fred,
My apologies for the delay in responding -- had to divert to another project. I'm back on this project now. Thank you for your response. Your fix worked beautifully. -- heyredone "Fred Smith" wrote: Your problem is that if any one of your cells has an error (like S2), then you get the dash. However, in your case if S2 has an error, you still want the total of O2 and Q2. So try it this way: =if(iserror(s2+0),if(iserror(o2+q2),"-",o2+q2),o2+q2+s2) However, this begs the question as to why you are creating an error when the employee has worked zero hours. Why not enter 0 when no hours are worked? Then you can simply sum the hours (o2+q2+s2) without needing to check for errors. If you want a dash displayed when the hours are zero, you're better off to use a custom format like: 0.0;-0.0;- This way you get the display you want, but can still do arithmetic on the cell. Regards, Fred. "heyredone" wrote in message ... I have a worksheet with breakdown calculations of hours worked in a given day for a person (morning block of time, afternoon block of time & possibly a third block of time). I am now attempting to calculate the total number of hours a person worked for a given day. I have used =IF(ISERROR(O2+Q2),"-",O2+Q2) with cells O2 & Q2 being hours worked in a block of time. Some people have a 3rd block of time in a day (column S) and if I try =IF(ISERROR(O2+Q2+S2),"-",O2+Q2+S2) it either gives me "-" or an error, which I know to not be an accurate result. What am I doing wrong? Thank you! -- heyredone |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISERROR with more than 2 cells of data
Glad I could help. Thanks for the feedback.
Regards, Fred. "heyredone" wrote in message ... Fred, My apologies for the delay in responding -- had to divert to another project. I'm back on this project now. Thank you for your response. Your fix worked beautifully. -- heyredone "Fred Smith" wrote: Your problem is that if any one of your cells has an error (like S2), then you get the dash. However, in your case if S2 has an error, you still want the total of O2 and Q2. So try it this way: =if(iserror(s2+0),if(iserror(o2+q2),"-",o2+q2),o2+q2+s2) However, this begs the question as to why you are creating an error when the employee has worked zero hours. Why not enter 0 when no hours are worked? Then you can simply sum the hours (o2+q2+s2) without needing to check for errors. If you want a dash displayed when the hours are zero, you're better off to use a custom format like: 0.0;-0.0;- This way you get the display you want, but can still do arithmetic on the cell. Regards, Fred. "heyredone" wrote in message ... I have a worksheet with breakdown calculations of hours worked in a given day for a person (morning block of time, afternoon block of time & possibly a third block of time). I am now attempting to calculate the total number of hours a person worked for a given day. I have used =IF(ISERROR(O2+Q2),"-",O2+Q2) with cells O2 & Q2 being hours worked in a block of time. Some people have a 3rd block of time in a day (column S) and if I try =IF(ISERROR(O2+Q2+S2),"-",O2+Q2+S2) it either gives me "-" or an error, which I know to not be an accurate result. What am I doing wrong? Thank you! -- heyredone |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iserror help | Excel Discussion (Misc queries) | |||
iserror help | Excel Worksheet Functions | |||
Do Not count iserror cells | Excel Discussion (Misc queries) | |||
ISERROR - #NAME? | Excel Worksheet Functions | |||
iserror | Excel Discussion (Misc queries) |