0

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.

5
  • The inequalities aren't numbers, so you can't include them in calculations. Also, their value is not equivalent to the value excluding the "<".
    – fixer1234
    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.
    – martin
    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.
    – fixer1234
    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!
    – martin
    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.
    – fixer1234
    Apr 21, 2016 at 7:36

1 Answer 1

2

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.

enter image description here

1
  • 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,"<","")))
    – martin
    Apr 22, 2016 at 7:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .