Using ISERROR with more than 2 cells of data

[heyredone]

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!

 

[JE McGimpsey]

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:"-";@

 

[Fred Smith]

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]

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.

 

[Fred Smith]

Glad I could help. Thanks for the feedback.

Regards,
Fred.