Is there a sum inverse function?

lynx20
lynx20 used Ask the Experts™
on
Hi All:

Is there a function that will sum the inverse of a range of cells, which also allows for zeros as some of the values?  The same as: 1/A1 + 1/B1 + ... +1/H1  and allow for a 0 value in B1 & E1.

Bob
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
=SUM(IF(ISERROR(1/A1:A10),0,1/A1:A10))

This is an array formula, so enter with Ctrl + Shift + Enter

HTH
Darren
Commented:
or more suitably...

=SUM(IF(ISERROR(1/A1:H1),0,1/A1:H1))

Entering as an array formula should insert the formula in {}

Author

Commented:
I get an error when I enter.  It highlite the 0 in the formula.

Bob
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Commented:
could you upload a sample?

Author

Commented:
I cannot upload the entire workbook so I created a value only version of the sheet.  I am attempting to enter the formula in U5.
Test.xls

Commented:
You've missed a ")"

Should be   =SUM(IF(ISERROR(1/BC5:BJ5),0,1/BC5:BJ5))

Author

Commented:
Thanks.  I guess I've been staring at this thing for too many days.

Commented:
No problem, I know the feeling, I assure you!

Cheers for the grade!
Darren

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial