Formula won't ignore cell

I've come across a weird problem with one of my formulas, and I've recreated it in the attached spreadsheet.

I've got some data and a formula that uses it - D7.

At times, some of the data may not be available - say, for example, I5 is empty. This then gives #DIV/0 errors in all forumulas that use it.

I've put a trigger on my spreadsheet so that if the circumstances are such that any data is missing, there will be a "2" in D5. This is supposed to then remove the formula in D7.

The result (D13) shouldn't now have an error code.

The problem is, if there is a "2" in D5, D7 then comes up blank, but I still get a different answer than if I clear the contents of D7 by manually deleting the forumula.

These aren't the actual formulas I'm using, I just put them in to show what I'm talking about.

Hope this makes sense!

Should the result be "fail"?

If so replace your D7 formula with:

=IF(D5=2,0,AVERAGE(J5:J10)) You can custom format to hide 0 as: 0;-0;;@

The reason is that a "" is not the same as a complete blank cell..

When you are comparing with D7>D9, it sees the null as text.. but a blank cell as 0, which are different... text strings are greater than numbers, in the eyes of Excel....