MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### i want zero value

• Follow

```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(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))
```
 0
Reply Utf 12/28/2009 8:39:01 PM

```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
O27>P27, 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(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))
to
=IF(O27<=P27,O27*K27/J27)+IF(O27>P27,O27*K27/J27+AQ27*K27/J27)+IF(U27<=V27,U27*K27/J27)+IF(U27>V27,U27*K27/J27+AU27*K27/J27)+IF(AA27<=AB27,AA27*K27/J27)+IF(AA27>AB27,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+(O27>P27)*AQ27*K27/J27+U27*K27/J27+(U27>V27)*AU27*K27/J27+AA27*K27/J27+(AB27>AA27)*AY27*K27/J27

You can trap the J27 divide by zero problem by changing that to
=IF(J27=0,0,O27*K27/J27+(O27>P27)*AQ27*K27/J27+U27*K27/J27+(U27>V27)*AU27*K27/J27+AA27*K27/J27+(AB27>AA27)*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+(O27>P27)*AQ27+U27+(U27>V27)*AU27+AA27+(AB27>AA27)*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(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))

```
 0
Reply David 12/28/2009 9:02:49 PM

```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(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))
```
 0
Reply Utf 12/29/2009 2:30:01 PM

2 Replies
173 Views

(page loaded in 0.175 seconds)

Similiar Articles:

7/20/2012 9:57:27 AM