How to find errors (formula errors) in a range

Question

Level 1

5 points

How to find errors (formula errors) in a range

This seems like it should have been asked in the forums before but I don't see any posts, so:

How do I use a formula to find errors (formula errors) in a range?

In my example below, I am trying to make a formula in the header columns of B & C that find cells in its respective column that are errors, non-numbers, or negative numbers. I have figured out that I can use REGEX to find the non-numbers and negative numbers, but I can't figure out a way to look for errors.

I realize I can have a formula in a hidden column (D) that looks for this on the individual row level (as pictured below), but I don't want to have a formula in every row like this, as I imagine it will slow down the spreadsheet with a large data set. When I use "ISERROR" in the header column and select a range of cells, it gives me TRUE even if those cells are not errors.

Posted on Jun 13, 2023 10:43 AM

Reply

Answer 1

Badunit

Level 7

30,348 points

Jun 15, 2023 4:24 AM in response to PuddleOfFat

I don't yet have a perfect solution but ISERROR(SUM(B)) is okay at flagging error triangles. It will, however, also flag a duration as an error because you cannot sum a duration and regular numbers. So far, this is what I have:

=IFS(ISERROR(SUM(B)),"error",COUNTIF(B,"<0")>0,"negative",COUNTIF(B,">=0")≠COUNTA(B),"non number",TRUE,"all good")

This will flag a date as a non-number but two or more dates as an error, because you cannot sum dates.

I'll give it some more thought. It might take a couple of additional IF conditions in the proper order to get it all right.

Reply

Answer 2

SGIII

Level 8

37,532 points

Jun 16, 2023 2:05 AM in response to PuddleOfFat

Taking this approach further.

This one formula will check for non numbers and if cells in both columns blank on a row are blank (unless they are at the bottom).

It will show an error if there is a formula error in the columns.

The formula in B1:

=IF(COUNTMATCHES(TEXTJOIN("~",0,B:C),REGEX("[^\d,.~-]|~~~~"))=0,"bueno","no-bueno")

The regex matches anything that is not a digit or a , or a . or a ~ or a -. It also matches four consecutive ~ (which occur when there are two blank cells on a row within the data).

The string formed by TEXTJOIN looks something like this in the "bueno" case:

1~~~1,869.98~10.00~~18.34~~~40.00~~

Or, in the bad cases, something like one of these:

1~~~1,869.98~10.00~~18.34~~b~~~40.00~~

1~~~1,869.98~10.00~~18.34~~January 1~~~40.00~~

1~~~1,869.98~10.00~~18.34~~~~~40.00~~

Checking for errors is "built-in" with TEXTJOIN as described in the other post so the same formula as above produces this.

SG

Reply

Answer 3

PuddleOfFat Author

Level 1

5 points

Jun 15, 2023 10:44 AM in response to Badunit

Here is what a I came up with (until I ran into a different problem discussed below):

IF(AND(COUNTA(J)−COUNTIF(J,REGEX("^(\d{1,3}((\,\d{3})+)*\.\d{2})$",))=0,ISERROR(XMATCH(0,J,−1,−1))),"bueno",”no-bueno”)

The REGEX just matches the exact format that I have created for my numbers in columns B & C.

I then realized that I wanted my formula to check if two cells in the same row for columns B & C (ex: B1263 & C1263) were neither both blank nor both occupied/not blank. Again, also doing this via a formula in the header columns, not via formulas in each individual rows.

I submitted a separate question about it, since I felt it was unrelated to the initial question I asked.

See my other post here: How to find blank cells in a range? - Apple Community

Reply

Answer 4

SGIII

Level 8

37,532 points

Jun 16, 2023 12:38 AM in response to PuddleOfFat

See my reply in the other thread.

As for flagging whether there are any formula errors in the two columns you can do this:

The formula in B1, that checks columns B and C:

=IF(COUNTMATCHES(TEXTJOIN("~",0,B:C),"~~~")>0,"","")

If you want to check both for adjacent blank cells and for errors in one formula then you can just put something between the first pair of "" , which is the formula suggested in the other thread:

=IF(COUNTMATCHES(TEXTJOIN("~",0,B:C),"~~~")>0,"adjacent blanks","")

SG

Reply

Answer 5

PuddleOfFat Author

Level 1

5 points

Jun 19, 2023 8:01 PM in response to SGIII

Thank you! Good to know that TEXTJOIN can also be used as a way to detect errors! Like Badunit's suggestion, it's too bad there's not a built-in proper way to check for errors other than these workarounds (at least they seem like workarounds to me). All in all, this works well!

Though as for the blanks, I'm not sure if searching for "~~~" will be sufficient. I'll expand on this in the other thread, since I feel it's off-topic here at this point, seeing as this thread is about the error aspect of my problem. Sorry for the confusion!

Reply