numbers show errors as 0

Question

Level 1

14 points

numbers show errors as 0

Hey Folks,

I have been an Apple guy for 20 years and have always been a fan of iWork. I have usually only used Pages and Keynote, but I have recently started using Numbers a lot more for work and I have a question about formulas / formatting / warnings.

I am calculating the difference in hours between 2 cells. I would like to be able to leave cells blank and just have the product be a "0" or a blank until the pertinent cells are filled in. The warnings just are irritating to look at and they affect 3 other columns in my spreadsheet.

I hope my question makes sense, but I am happy to clarify if you need me to.

Thanks in advance for the help!

Numbers '09-OTHER, OS X Mountain Lion (10.8.2)

Posted on Jan 22, 2013 6:20 AM

Reply

Answer 1

Best reply

Jerrold Green1

Level 7

30,210 points

Jan 22, 2013 8:52 AM in response to HeyAppleGuy

Guy,

Check out the IFERROR function. It will trap the error and allow you to substitute a message, or a null string, or a zero for the errror icon.

Jerry

Reply

Answer 2

HeyAppleGuy Author

Level 1

14 points

Jan 24, 2013 6:25 AM in response to Jerrold Green1

Thanks Jerry! This looks like just the thing. However, I am now just getting syntax errors. The cell in question already contains this formula:

=PRODUCT((TIMEVALUE(Time Out '1/21/13'))-(TIMEVALUE(Time In '1/21/13')), 24)

I have set it up so I can type in a time in 2 cells and it will convert the times to a value and return the difference. Like a time clock. I have never tried to put more than one formula in a cell. Is this possible?

Thanks so much for the help, I feel like this is really close.

Guy

Reply

Answer 3

HeyAppleGuy Author

Level 1

14 points

Jan 24, 2013 6:58 AM in response to HeyAppleGuy

GOT IT!

Formula now looks like this.

=IFERROR(PRODUCT((TIMEVALUE(Time Out '1/21/13'))-(TIMEVALUE(Time In '1/21/13')), 24),0)

and returns a 0 in the hours cell if either time is blank.

Thanks for pointing me in the right direction Jerry! Reply so I can mark that you solved. 😀

Guy

Reply

Answer 4

Jan 24, 2013 9:16 AM in response to HeyAppleGuy

Guy,

Thanks for the credit. I'm glad you have it under control.

Jerry

Reply