#DIV/0 error for two cells

[bbenaway]

I am dealing with two cells to get a percentage. Either cell can begin with a zero, giving me a #DIV/0 error. Currently, as seen below, cell F6 is set to deal with the #DIV/0 error. How can I set this as both cells F6 and H6 can handle the #DIV/0 error.

=IF(F6=0,0,(H6-F6)/ABS(F6))

See attached photo.

 

[SkipVought]

Hi,

Either cell can begin with a zero

If you have cell values that BEGIN with zero, assuming no formatting for leading ZEROS, you have TEXT and not NUMBERS.

To correct this,

1. Enter the value 1 in an enpty cell.

2. COPY this cell.

3. Select the cells with data in columns H & F You can do these one at a time)

4. Edit > Paste Special - MULTIPLY



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluedragon2]

=IF(OR(F6=0, F6-H6=0),0,(H6-F6)/ABS(F6))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Blue,

The OP is not DIVIDING by (H6-F6)

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Or just get rid of the error if it happens, using iserr:
=IF(ISERR(A1/B1), "X", A1/B1)
Replace "X" with 0 if you want it to return zero in the case that the divisor is zero. I personally don't like this, as 1/0 is not zero. Depending on context, I'd prefer a blank result ("") rather than a number that is incorrect.