Google Sheets conditional formatting and sum functions not working for =IFERROR cells

Question

Erik was able to help me out with an "aging" formula in the below post.

Show most recent date a theatre is visited Google Sheets

I've customized his formula to put out how many days old some of my inventory is using this formula.

=iferror(vlookup(P42,Aging!A:C,3,false),"MAX")

Going with the same example of the theatre visits, this would look up the theatre ID in P42, pull the days since last visit from "Aging" and return that value.

Now I would like to conditionally format the =iferror column to highlight cells over a certain number. However the only conditional formatting that is working is the "text is exactly" or "text contains". No greater than, less than, etc. conditional formatting works. I've tried changing the values to numbers, text, automatic, nothing works.

I also cannot SUM or AVERAGE the column even if I don't include the "MAX" AVERAGE returns #DIV/0! and SUM returns "0"

Hoping someone can advise what's going on!

Column Q in the picture contains the formulas that are in column P

Answer 1

The behavior you describe suggests that the values that look like 22 and 14 are not numbers but text strings that look like numbers. You can convert them to numbers with the value() function, like this:

=iferror( value( vlookup(P42, Aging!A:C, columns(Aging!A:C), false) ), "MAX" )