Formula returning unexpected result

[ddub25]

=IF(AO780=0,0,((AA780*AQ780)/AO780)*AP780)

I want the above forumla to return '0' IF AO780=0.

AO780 does = 0, but I am getting '#DIV/0!'

Why is this?

Dan

 

[Jonmo1]

Sounds like AO780 doesn't actually = 0

Could be a rounding issue..look in the formula bar for AO780, does it also show 0?

could also be that AO780 is a "number stored as text"

=ISNUMBER(AO780)
What does that return?

 

[JackDanIce]

Works fine for me. What about:

=IF(AO780<>0,(AA780*AQ780*AP780)/AO780,0)

jonmo's suggestion probably more likely to reveal where the error is

 

[Jonmo1]

It's not going to be a rounding issue though...after more thought...
If it was a rounding issue, and the cell was actually 0.0001 or whatever...
Then it wouldn't be returning #DIV/0!


But it's likely the "number stored as text" problem.

 

[JackDanIce]

Building on that, maybe you could try:

=IF((AO780+0)=0,0,(AA780*AQ780*AP780)/(AO780+0))

That should force AO780 to be a numerical value if it is indeed text..

 

[ddub25]

AO780 is formatted as a number.

When I try '=ISNUMBER(AO780)' FALSE is returned

AO780 contains:

=IFERROR(INDEX('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BX$5:$BX$1130,MATCH(1,IF('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130=B780,IF('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130=I780,1)),0)),"0")

The formula is referencing a value on another worksheet that you helped me with earlier Jonmo.

 

[JackDanIce]

Try the second formula I suggested; it forces AO780 to be a numerical value by adding '0' to whatever it contains. You can test that by trying:

=ISNUMBER(AO780+0)

 

[Jonmo1]

change

"0"
to
0

 

[ddub25]

Thanks Jonmo, that solved it. Thanks Jack. I tried yours but could not get it to work.

Dan