SharePoint 2007 Calculated field displaying #DIV/0! How to suppress

[Jowidog]

All,

This should be very simple, but to be frank I am drawing a brain blank. I have two fields and I am trying to get a ratio. For the most part it works but if there is no value in Current Equipment, I get the famous #DIV/0! error that the command staff find a bit on the annoying side.

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

Any help with modification would be greatly appreciated.

Thanks in advance,

 

[SkipVought]

hi,

Why are you testing the NUMERATOR? that is not essential to the error.
[tt]
=IF([Current Equipment Count]="",0,ROUNDUP([Unit Strength]/[Current Equipment Count],0))
[/tt]


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

 

[Jowidog]

I will try your suggested change. It makes sense, however in the db there may be occasions where one or the other is blank (no value) or both may have values in the event of a contract renewal.

I formed the above query in that way because of the situation in another calc column on subtracting dates. And in that circumstance the calc column was always looking for a date value in either column.

Skip, I really appreciate your pointing me to this site and helping me through this.

Thanks again, headed to the office in a few and will try it.

Jim

 

[Jowidog]

Skip,

I am still getting the DIV/0! error. SP accepts the syntax but I have two records that have Unit Strength ( number of people) that is greater than 0 (ths is true in all cases) but they may not have any items on hand. I recognize that we are trying to mask the divisor if no value is true.

In my [Current Equipment Count] column it is set to number and I have just made 0 (zero) the default. I went into the two errant records and set the value of Current Equipment Count to 0 (zero) but no difference. Thoughts kind sir?

Thanks again in advance. Jim

 

[Jowidog]

PROBLEM SOLVED. 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 Cound 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.