error in formula

[marcus]

Hello everybody,
I'm having a problem with this formula:
=1/(1/b1+1/b2+1/b3+1/b4)
the problem is that sometimes I only enter two values, in cell b1 and
b2. Obviously that generates Div/0 error. How can I "tell" this
formula to calculate only cells that have something in it and ignore
empty ones.
Thanks for help,

Mark

 

[ed]

Try this UDF

Public Function nums(ParamArray values())

For i = 0 To UBound(values)
If values(i) <> 0 Then
newVal = newVal + 1 / values(i)
End If
Next

nums = 1 / newVal

End Function

Regards,
Eddie
http://www.HelpExcel.com

 

[ed]

I have copied the code to http://www.HelpExcel.com/examples. The name
of the spreadsheet is customFunction.xls.

 

[Bob Phillips]

=1/SUM(IF(B1:B4<>"",1/B1:B4))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[marcus]

=1/SUM(IF(B1:B4<>"",1/B1:B4))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

Thanks Bob, this works

Mark