Search code examples
excelexcel-formulaarray-formulas

Ignore specific symbol in cells before calculating the average of the column

I have a column of data in B7:B30:

<0.0012
<0.0013
<0.0014
<0.0015

And so on...

I want the average of these cells to appear in cell B31. But Excel sees the < symbol in each cell and won't return a value. I need the lesser than symbol to remain in each cell.

Is this possible?

Solution

  • Please try: 

    =SUM(VALUE((SUBSTITUTE(B7:B30,"<",""))))/COUNTA(B7:B30)

    with Ctrl+Shift+Enter.

    Will fail if there are any blanks in the range.