I'm in the process of writing an excel sheet to do a series of calculations
and lookups etc. and have got to the final calculation and cant make the step
work!

I need to calculate the reciprocal of 6 numbers. i.e 1/a = 1/b+1/c+1/d etc.
fairly standard, but i dont always have a value and i want to make excel
ignore the step if there is no value in a particular cell (these are linked
from previous sheets and currently have =if(cellx = "yes", celly,"")).
However, when the "" is used for 1/b+1/c etc. i get a #value error.

Any ideas on ways around this would be greatly appreciated.

Hi,
I assume that if one of the values in a, b,c,d are "", to enter "" otherwise
to perform your calculation

=IF(OR(A2="",B2="",C2="",D2=""),"",1/A2+1/B2+1/C2+1/D2)

If this help please say yes, thanks

"Re." wrote:

I'm in the process of writing an excel sheet to do a series of calculations
and lookups etc. and have got to the final calculation and cant make the step
work!

I need to calculate the reciprocal of 6 numbers. i.e 1/a = 1/b+1/c+1/d etc.
fairly standard, but i dont always have a value and i want to make excel
ignore the step if there is no value in a particular cell (these are linked
from previous sheets and currently have =if(cellx = "yes", celly,"")).
However, when the "" is used for 1/b+1/c etc. i get a #value error.

Any ideas on ways around this would be greatly appreciated.

Use a CSE formula to test each of the cells before the function. this would
be easiest to design if I had an example of actual data and an actual result,
but hopefully this will get you in the right direction. Here are two sample
formulas.

In cell A9, this formula would examine B9, C9 and D9 and sum only the cells
with data
=1/SUM(IF(B9:D9<0,1/(B9:D9)))
or
=SUM(IF(B9:D9<0,1/(B9:D9)))

....but you MUST enter that formula by pressing CTRL-SHIFT-ENTER to activate
the array. you'll see braces { } around your formula if you do that and you
will get an answer instead of an error. You will still get an error when ALL
3 cells are empty.


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Eduardo" wrote:

Hi,
I assume that if one of the values in a, b,c,d are "", to enter "" otherwise
to perform your calculation

=IF(OR(A2="",B2="",C2="",D2=""),"",1/A2+1/B2+1/C2+1/D2)

If this help please say yes, thanks

"Re." wrote:

I'm in the process of writing an excel sheet to do a series of calculations
and lookups etc. and have got to the final calculation and cant make the step
work!

I need to calculate the reciprocal of 6 numbers. i.e 1/a = 1/b+1/c+1/d etc.
fairly standard, but i dont always have a value and i want to make excel
ignore the step if there is no value in a particular cell (these are linked
from previous sheets and currently have =if(cellx = "yes", celly,"")).
However, when the "" is used for 1/b+1/c etc. i get a #value error.

Any ideas on ways around this would be greatly appreciated.