1

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?

spreadsheet

3

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)
1
  • Most excellent, dude! – K7AAY Oct 1 '18 at 20:58
1

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:

enter image description here

1
  • @nutsch provided an answer below which does exactly what the OP wanted. It's a great answer. – K7AAY Oct 1 '18 at 22:34
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.