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?
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:
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:
This is a bit of a kludge and assumes you don't mind an extra sheet in your workbook. If your table is in
Sheet2, use the following formula:
Then copy down and across to match the dimensions of the table in
=AVERAGE(Sheet2!A1:A12) and copy across for each column
Sheet2 can be hidden if need be, it won't affect the formulas)