I have columns of laboratory analytical data in Excel. Some cells contain the <
symbol and some do not. I need a formula that tells Excel to average the columns, but to use 1/2 the value for the cells containing the <
symbol. Do you have any ideas?
If you want to do without the helper column, the following formula will give you the average for column A:
=SUMPRODUCT(SUBSTITUTE(A1:A12,"<","")*(1/2+1/2*(LEFT(A1:A12,1)<>"<")))/COUNTA(A1:A12)
First, here's a formula to normalize the values, because any value beginning with '<' will be considered a string value, which Excel cannot average or do any other mathematical operation upon.
Create a target range, with the same number of rows and columns as the source data range; your example has a 12 row by 4 column matrix, so the destination will also be 12x4. Put this formula in each cell of the destination range, so this operation is performed on every cell in the target range, from A1 to D12:
=IF(ISNUMBER(SEARCH("<",A1)),RIGHT(A1,LEN(A1)1)/2,A1)
will look at cell A1.
If its value begins with '<' then it will strip off the leading '<' and divide the remaining numerical value by 2.
If its value does not begin with '<', that formula will return its original value.
Once you have a matrix of exclusively numeric values, then below row 12, average each column with =AVERAGE(X1:X12) where X is the column's designation. Here's a screenshot of the result:

@nutsch provided an answer below which does exactly what the OP wanted. It's a great answer. – K7AAY Oct 1 '18 at 22:34
This is a bit of a kludge and assumes you don't mind an extra sheet in your workbook. If your table is in A1:D4
on Sheet1
On Sheet2
, use the following formula:
=IF(LEFT(Sheet1!A1,1)="<",RIGHT(Sheet1!A1,LEN(Sheet1!A1)1)/2,Sheet1!A1)
Then copy down and across to match the dimensions of the table in Sheet1
.
Back on Sheet1
, use =AVERAGE(Sheet2!A1:A12)
and copy across for each column
(Sheet2
can be hidden if need be, it won't affect the formulas)