Search code examples
arraysregexgoogle-sheetsaveragegoogle-sheets-formula

Evaluation of function AVERAGE caused a divide by zero error

In Google sheets, I just input the following values through Data Validation.

Data Validation Values

Once the range of values are input in a google sheet, it looks like the following:

Data range values in cell

From the above figure:

I want to take an average of inputted drop down values in the range between B6 and B14.

I applied a formula =Average(B6,B14). But, it display error message.

Error!

Evaluation of function AVERAGE caused a divide by zero error.

Anyone knows how to resolve it. Thanks in Advance!

Solution

  • You can use the following formula:

    =AVERAGE(ARRAYFORMULA(REGEXEXTRACT(B6:B14, "\d+")*1))

    Explanation

    The =REGEXTRACT() function will extract the first match of a regular expression for the given string. In this case, the first chain of numbers will be extracted.

    Afterwards, the result of the function is multiplied by 1. This is done to transform the type of the result of =REGEXTRACT() from a string to a number, so that we may afterwards calculate the average.

    =REGEXTRACT(), by default, operates on single strings - but not on multiple cells at the same time. For that reason, we have to wrap the call with =ARRAYFORMULA() which will allow to call it with a range as a parameter.

    Finally, the =AVERAGE() function is called.

    Handling empty cells

    With the previous response, given a range in which at least a cell is empty/doesn't conform the regex specified in the =REGEXTRACT() call, an error will be returned. We have two main options to handle this situation:

    1. Ignore the cell. The cell will be completely ignored for the average computation:

      =AVERAGE(ARRAYFORMULA(IFERROR(REGEXEXTRACT(B6:B14, "\d+")*1, "")))

    2. Treat the cell as a 0 towards the average computation.

      =AVERAGE(ARRAYFORMULA(IFERROR(REGEXEXTRACT(B6:B14, "\d+")*1, 0)))

    Both the solutions use the =IFERROR() function. In case the call to =REGEXTRACT() returns an error, the value will be replaced respectively for an empty string (which doesn't count in the average computation) or for a 0 instead.