Suppressing #DIV/O! error in SharePoint 2007 calculated column

[Jowidog]

All, I have a fairly straight foward issue. MS SharePoint 2007, view with calculated columns.
I have a calculated column that has the following forumla
=IF(OR([Field]=””,[field2]=””),0,ROUNDUP([field1]/[field2],0))

ISSUE: If Column 2 does not have a value then the dreaded #DIV/0! error shows up.
However if there are no values in Field 1 or 2 the calculated column returns 0 as it should.

I would like: either to return 0 or a text expression of None On Hand. We are measuring the ratio of equipment on hand to people. However there are times when there is no equipment on hand and this is a new purchase so the Field 2 would be blank.

I appreciate any assistance.

Jowidog

 

[SkipVought]

hi,

There is forum820.

In Excel there is an IFERROR() function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Or can you just 'wrap' [blue]your formula[/blue] in another little IF statement?
[tt]
=IF(field2="",0,[blue](IF(OR([Field]="",[field2]=""),0,ROUNDUP([field1]/[field2],0))[/blue] ))
[/tt]

Have fun.

---- Andy

 

[Jowidog]

Andy,

Thanks! However the #DIV/0 error still shows: SharePoint List View 2007. Both fields are numbers so we are not mixing apples with oranges.

Here is my syntax:

=IF([Current Equipment Count]="",0,(IF(OR([Unit Strength]="",[Current Equipment Count]=""),0,ROUNDUP([Unit Strength]/[Current Equipment Count],0))))

Can you spot the issue? SharePoint accepts the syntax and formula as valid. Unit strength is people and Equipment is the number of items on hand. Trying to determine a ratio of people to currently held assets.

Thanks for your help.

 

[Jowidog]

Skip and Andy, I solved it thanks to both of your inputs:

An old instructor once told me to "talk the formula through through out loud"....I am such a dunce.

Here is the fix because the Current Equipment Count defaults to ZERO, the value is Zero and should be represnted as 0 (ZERO) and don't do a calculation if "none of your friends are at home".

=IF(OR([Current Equipment Count]=0),0,ROUNDUP([Unit Strength]/[Current Equipment Count],0))

I SINCERELY appreciate the help, I guess this does boil down to KEEP IT SIMPLE STUPID and stick to the basics. (That would be me yelling at me)