Formula not recognizing value in referenced cell

[klynshoe]

I have a formula that references the numeric value of another cell with a formula. It doesn't work.... how can I fix this?

Non-Working Formula: =IFERROR(IF(OR(F8/D8>0.05,F8=E8),"X",""),"")

Referenced Formula in F8: =IF(E8="","",IFERROR(E8-D8,""))

Where the value in cell F8 and E8 is both 11, the result should be "X" since F8=E8 but it does not, it gives me "" (blank).


USING EXCEL 2010

Thank you,

Kelly

 

[Scott T]

What is in D8? Since you are dividing by D8 if it is blank you have a divide by zero error and if error returns blank.

 

[Norie]

What's the value in D8?

 

[C with no eyes]

Try changing one of your "" to "error" so you can find out which stage it is going wrong.

In fact, I can't see why you have an iferror there. Do the cells sometimes contain text?

I had a quick play around. Could you take the iferror off and confirm whether or not the error is a DIV error?

 

[Jonmo1]

I'm guessing that D8 is 0 (or blank, which would be treated the same as 0)
So F8/D8 returns a #Div/0! Error.
That error Propegates to the overall result of the OR, regardless of the result of the 2nd argument in the OR.
So the OR = #Div/0!, therefore it's trapped by Iferror, and returns "".

You need to independently test for errors.
And sometimes it's easier to test for the condition that causes the error
#Div/0! is caused by the devisor being = 0

Try
=IF(D8=0,"",IFERROR(IF(OR(F8/D8>0.05,F8=E8),"X",""),""))

 

[klynshoe]

D8 is also a formula: =IFERROR(VLOOKUP(C8,Labor[[Column1]:[FY18 EAC]],VLOOKUP('Change Request'!$B$4,'Change Request'!$K$16:$L$29,2,FALSE),FALSE),"0")

 

[klynshoe]

D8 is producing a "0" result from this lookup formula: =IFERROR(VLOOKUP(C8,Labor[[Column1]:[FY18 EAC]],VLOOKUP('Change Request'!$B$4,'Change Request'!$K$16:$L$29,2,FALSE),FALSE),"0")

The problem with your solution is that I still want an "X" if F8=E8, when D8 = 0. And if D8 does equal zero, shouldn't the "F8=E8" override that in the OR function?

 

[klynshoe]

What is in D8? Since you are dividing by D8 if it is blank you have a divide by zero error and if error returns blank.

Shouldn't the OR function overrule the divide by zero? The value in D8 is a formula and is resulting in zero, but the OR function says that if F8=E8, then it should be "X".

 

[klynshoe]

Try changing one of your "" to "error" so you can find out which stage it is going wrong.

In fact, I can't see why you have an iferror there. Do the cells sometimes contain text?

I had a quick play around. Could you take the iferror off and confirm whether or not the error is a DIV error?

Yes, I removed if IFERROR and it is indeed a DIV error.

 

[Norie]

Are you sure the value returned by the formula in D8 is exactly 0?