1

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

Column Q contains the formulas that are in column P

1
  • It is always most efficient to share a link to a spreadsheet, so that we can see all of the pieces quickly and at once. Feel free to share a link and then comment below that you have (so I get a notice). If you do that, I will be happy to take a look at the issue you're trying to solve. Do see @doubleunary's formula below, as that may very well solve the problem. We're just left to guess if we can't see the data and test formulas in context.
    – Erik Tyler
    Sep 8, 2022 at 19:28

1 Answer 1

0

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" )

1
  • That did it! Thanks so much!
    – CellbyDate
    Sep 8, 2022 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.