error in formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marcus@hotmail.com - 20 Feb 2007 14:46 GMT

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

Reply to this Message

ed@HelpExcel.com - 20 Feb 2007 14:58 GMT

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

Reply to this Message

ed@HelpExcel.com - 20 Feb 2007 15:03 GMT

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

Reply to this Message

Bob Phillips - 20 Feb 2007 15:27 GMT

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

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

Signature

---
HTH

Bob

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

> Hello everybody,
> I'm having a problem with this formula:
[quoted text clipped - 6 lines]
>
> Mark

Reply to this Message

marcus@hotmail.com - 20 Feb 2007 15:57 GMT

>=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

Reply to this Message