Cannot find 0's or blank cells

[lisaspencer]

I've got a column with formulas all the way down. The values end up being accounting figures. Some have figures, some are £0.00. I want to 'find' all the 0's so I can quickly delete them from the list. But I've tried every way on the 'Find' method and the 'Go to special' method and it either lists the digit '0' which appears in figures such as "£12.90", or it highlights them all because I've likely put in the wrong digit. Screenshots below to show. I've tried every option of 'match case' or 'match entire cell contents' and both at the same time, and i've tried no formatting and all versions of formatting using currency, accounting, general, number etc. Not sure why it won't find the blank or 0 cells even though it is using a formula

 

[Joe4]

Select the "Match entire cell contents" option to only return values EXACTLY returning 0, and not containing 0 (like $16.90).

What exactly is the formula in that column that returns those values?

 

[lisaspencer]

The formula is just =IFERROR(ROUND(SUM((I19*J19)*100),2),"")
column J is =IFERROR(VLOOKUP($D19,'List of courses'!$A$2:$F$83,6,FALSE),"")
column I is =IFERROR(G19-E19," ")

and they're all accounting figures

I've selected "Match entire cell contents" option, but how do I only return values EXACTLY returning 0?

 

[Joe4]

Your last one will return a space, not a blank, in case of error. I would recommend having all them return 0 in case of error, i.e.

=IFERROR(ROUND(SUM((I19*J19)*100),2),0)
=IFERROR(VLOOKUP($D19,'List of courses'!$A$2:$F$83,6,FALSE),0)
=IFERROR(G19-E19,0)​

 

[lisaspencer]

Thanks for the tip. I've changed them all to 0s instead of spaces or blanks. But they're still not showing up when I 'Find' 0's in the find box

 

[Joe4]

Please show me your current Find settings again, as you have them now.

 

[lisaspencer]

The column now says: =IFERROR(IF(ROUND(SUM((I19*J19)*100),2)=0,0,ROUND(SUM((I19*J19)*100),2)),0)

When you click 'Find All' it says there are no cells applicable

I've attached the format too:

 

[Joe4]

What exactly does column I and J data look like?
Are there errors in there?
If so, are they the result of formulas, and if so, what are those exact formulas.

By the way, your use of SUM in those formulas is unnecessary and redundant.
This:

SUM((I19*J19)*100)

can be simplified to just this:

(I19*J19*100)

SUM serves no purpose here, as you are not "summing" any range of cells in this formula - you are multiplying them!

 

[Fluff]

I've encountered this before & it's to do with the cell format.
Unfortunately I cannot remember how to get round it, other than changing the format to general.

 

[Joe4]

I've encountered this before & it's to do with the cell format.
Unfortunately I cannot remember how to get round it, other than changing the format to general.

Interesting.
That's a new one to me!