Formula function for ignoring cells/results equal to 0 (zero) ?
 

Assume I want to setup a formula for a cell which should calculate the result from several other cells
similar to

C1=K5/k6 + U8/U9 + M12/M13

As you can see if the value of the cells K6 or U9 or M13 are zero there is a division by zero
and the calculation crashes. In this case the terms should be ignored.

What I need now is a precalculation-and-ignore-instruction which excludes the terms similar to

C1=ignoreiftrue(K6=0,K5/k6) + ignoreiftrue(U9=0,U8/U9) + ignoreiftrue(M13=0,M12/M13)

In more general form the function

ignoreiftrue(<condition,<expression)

should evaluate at first the condition and if it is NOT true uses the expression(=function) as usual.

Is there such a function in Excel?

Keith

Formula function for ignoring cells/results equal to 0 (zero) ?
 

Since you are doing addition, zeroes won't affect the total, so you could do
this...

=IF(K6=0,0,K5/K6)+IF(U9=0,0,U8/U9)+IF(M13=0,0,M12/M13)

--
Rick (MVP - Excel)


"Keith Clark" wrote in message
...
Assume I want to setup a formula for a cell which should calculate the
result from several other cells
similar to

C1=K5/k6 + U8/U9 + M12/M13

As you can see if the value of the cells K6 or U9 or M13 are zero there is
a division by zero
and the calculation crashes. In this case the terms should be ignored.

What I need now is a precalculation-and-ignore-instruction which excludes
the terms similar to

C1=ignoreiftrue(K6=0,K5/k6) + ignoreiftrue(U9=0,U8/U9) +
ignoreiftrue(M13=0,M12/M13)

In more general form the function

ignoreiftrue(<condition,<expression)

should evaluate at first the condition and if it is NOT true uses the
expression(=function) as usual.

Is there such a function in Excel?

Keith

Formula function for ignoring cells/results equal to 0 (zero) ?
 

=IF(ISERROR(K5/K6),0,K5/K6) + .... + ...

If this post helps click Yes
---------------
Jacob Skaria


"Keith Clark" wrote:

Assume I want to setup a formula for a cell which should calculate the result from several other cells
similar to

C1=K5/k6 + U8/U9 + M12/M13

As you can see if the value of the cells K6 or U9 or M13 are zero there is a division by zero
and the calculation crashes. In this case the terms should be ignored.

What I need now is a precalculation-and-ignore-instruction which excludes the terms similar to

C1=ignoreiftrue(K6=0,K5/k6) + ignoreiftrue(U9=0,U8/U9) + ignoreiftrue(M13=0,M12/M13)

In more general form the function

ignoreiftrue(<condition,<expression)

should evaluate at first the condition and if it is NOT true uses the expression(=function) as usual.

Is there such a function in Excel?

Keith