i have this formula & i want to end up with 0 value if other cells read zero
IF G27 = ZERO i get a DIV/0 ERROR

=IF(O27<=P27,O27*K27/J27)+(IF(O27P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27AB27,AA27*K27/J27+AY27*K27/J27))))))

The only value which would give a DIV/0 error is if J27 was 0. [I don't
know why you refer to G27, as it doesn't appear in the formula.]

Note also that if you test for O27<=P27, you then don't need to test for
O27P27, and similarly in the latter tests.
Also, you have numerous unnecessary parentheses which can confuse you in
reading the formula and simplifying it.

You could simplify
=IF(O27<=P27,O27*K27/J27)+(IF(O27P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27AB27,AA27*K27/J27+AY27*K27/J27))))))
to
=IF(O27<=P27,O27*K27/J27)+IF(O27P27,O27*K27/J27+AQ27*K27/J27)+IF(U27<=V27,U27*K27/J27)+IF(U27V27,U27*K27/J27+AU27*K27/J27)+IF(AA27<=AB27,AA27*K27/J27)+IF(AA27AB27,AA27*K27/J27+AY27*K27/J27)
and then to
=IF(O27<=P27,O27*K27/J27,O27*K27/J27+AQ27*K27/J27)+IF(U27<=V27,U27*K27/J27,U27*K27/J27+AU27*K27/J27)+IF(AA27<=AB27,AA27*K27/J27,AA27*K27/J27+AY27*K27/J27)
or to
=O27*K27/J27+(O27P27)*AQ27*K27/J27+U27*K27/J27+(U27V27)*AU27*K27/J27+AA27*K27/J27+(AB27AA27)*AY27*K27/J27

You can trap the J27 divide by zero problem by changing that to
=IF(J27=0,0,O27*K27/J27+(O27P27)*AQ27*K27/J27+U27*K27/J27+(U27V27)*AU27*K27/J27+AA27*K27/J27+(AB27AA27)*AY27*K27/J27)

As you have a common factor of K27/J27, why not take that out and change to
=IF(J27=0,0,K27/J27)*(O27+(O27P27)*AQ27+U27+(U27V27)*AU27+AA27+( AB27AA27)*AY27)
?
--
David Biddulph


Mike wrote:
i have this formula & i want to end up with 0 value if other cells
read zero IF G27 = ZERO i get a DIV/0 ERROR

=IF(O27<=P27,O27*K27/J27)+(IF(O27P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27AB27,AA27*K27/J27+AY27*K27/J27))))))

Assuming that you mean "IF J27=ZERO" rather than G27, start off your IFs with
if(J27=0,0, IF(... to prevent the DIV/0 error.
Also, once you have tested for <=, there is no need to test for as it is
the only other option.
Also, also none of your if statements contains a FALSE clause.

"Mike" wrote:

i have this formula & i want to end up with 0 value if other cells read zero
IF G27 = ZERO i get a DIV/0 ERROR

=IF(O27<=P27,O27*K27/J27)+(IF(O27P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27AB27,AA27*K27/J27+AY27*K27/J27))))))