# Statistics in excel including modified text values

I have a table in Excel (2013) that looks something like this:

``````V1    V2    V3     V4
2.1   300   40.5   6.89
6.9   204   <0.02  <0.001
<0.3  96    6.5    1.2
``````

For each of the columns V1 through V4 I'd like to do some basic statistics, like mean, max and min values.

However, I would also like to include the values with the `<` symbol. But these cells are interpreted as text, and therefore excluded from the formulas.

How can I calculate this without having to manually/semi-autmatically remove the `<` symbol? I also like to avoid having to copy everything into a new sheet, since that is prone to error when entering new values.

I have tried different IF statements, but haven't been able to get around that they work on a cell by cell basis.

• The inequalities aren't numbers, so you can't include them in calculations. Also, their value is not equivalent to the value excluding the "<". Apr 21, 2016 at 7:02
• The first one I figured out, but was looking for a way to use the value after `<`. If that's not possible I guess I have to create a second sheet that copies everything from the first one except those symbols, and then do my statistics there. Impractical, but if that's the only way I'll take it. I know it's not equivalent to the value alone but it's a reasonable approximation in this case, and is way better than just excluding the values completely. Apr 21, 2016 at 7:07
• You could do something like replace the cell reference with a formula; say for A2: =IF(LEFT(A2,1)="<",VALUE(MID(A2,2,LEN(A2)-1)),A2) If you want to apply stats to ranges, create helper columns with the sanitized values and then work with that. Apr 21, 2016 at 7:20
• Having some 15 columns and a several hundred rows of data I was hoping to avoid that, especially since these values will be manually updated quite often and rows will be added and removed. I guess I was asking too much this time :) Thanks anyway! Apr 21, 2016 at 7:28
• Enter the formula in the first cell and then copy or drag it to fill as many rows and columns as you need. If you wrap it in a test for a blank source cell and use NA(), you can pre-populate an arbitrary large range of cells to handle additions and deletions in the source data; blank source cells will produce a "not available" value which won't affect your stat calculations. Apr 21, 2016 at 7:36

To do this without helpers use `--SUBSTITUTE()` as the array:

``````--SUBSTITUTE(\$A\$2:\$D\$4,"<","")
``````

So to get the average:

``````=AVERAGE(--SUBSTITUTE(\$A\$2:\$D\$4,"<",""))
``````

To get the Median:

``````=MEDIAN(--SUBSTITUTE(\$A\$2:\$D\$4,"<",""))
``````

By doing this the formulas need to be entered as array formulas. To do so After typing the formula hit Ctrl-Shift-Enter instead of Enter, Tab, or clicking out the cell. If done properly Excel will put `{}` around the formula.

• Thanks! This is exactly what I was looking for! However, since I had a few empty cells in between I had to use this in the end: `=AVERAGE(IF(\$A\$2:\$D\$4<>"",(--SUBSTITUTE(\$A\$2:\$D\$4,"<","")))` Apr 22, 2016 at 7:19