0

I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text. If the cell contains text, it will only be an L to the right of the numeric value.

Example of cells I would like to take the average of: Average of Cells

I have tried something like this:

=Average(VALUE(LEFT(A1:H1,IF(RIGHT(A1:H1,L),VALUE(LEFT(A1:H1,LEN(A1:H1)-1)),VALUE(LEN(A1:H1))))))

which is an attempt to remove the L from the value if it's there, otherwise to just give the value in the cell. In my research, I have found people talking about using an array formula, but my attempts to make that work have been unsuccessful.

Edit: Another thing that would be ideal is if the function could skip over empty cells like the average function normally does.

Improve this question

2 Answers 2

Reset to default
2

EDIT

(following OP's comment)

To exclude empty cells (which are counted as zeros altering the result) we adjust the formula by adding the FILTER function.

=AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(FILTER(A44:H44,A44:H44<>"")),"L",""))*1))

Initial answer

You can use this simple formula:

=AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(A44:H44),"L",""))*1))

Functions used:

Improve this answer
3
  • This works really great for the example I have provided! The one issue that I am realizing is that if any of the cells that are being averaged are empty, this will count those as 0, and include them in the average. Normally, the average function will skip over empty cells, which is what I'd prefer if that's possible. Any ideas?
    – Clayton
    Commented Nov 15, 2019 at 16:50
  • 1
    Please have a look at the updated answer
    – marikamitsos
    Commented Nov 15, 2019 at 18:44
  • Amazing! Thank you so much!
    – Clayton
    Commented Nov 15, 2019 at 21:34
1

try this. It's cumbersome but it works =average(if(isnumber(A1),A1,value(left(A1,len(A1)-1))),if(isnumber(B1),B1,value(left(B1,len(B1)-1))),if(isnumber(C1),C1,value(left(C1,len(C1)-1))),if(isnumber(D1),D1,value(left(D1,len(D1)-1))),if(isnumber(E1),E1,value(left(E1,len(E1)-1))),if(isnumber(F1),F1,value(left(F1,len(F1)-1))),if(isnumber(G1),G1,value(left(G1,len(G1)-1))),if(isnumber(H1),H1,value(left(H1,len(H1)-1))))

The logic is that it evaluates the content of each cell:

  • if it is a number, then it use the number

  • if it is not a number then it is a string; use the number generated by the following...

    • calculate length of the string
    • return all the characters except the last one
    • convert that result into a number
  • Calculate average of the discrete numbers.

This could be simplified a little if it was known that the population consisted only of only 2 digit numbers. This would reduce each "test" by 8 characters (64 in total).

The average = 15.625

Improve this answer
2
  • Although it is bulky, this works really great for the example I have provided! The one issue that I am realizing is that if any of the cells that are being averaged are empty, this will cause an error in the calculation. Normally, the average function will skip over empty cells, which is what I'd prefer if that's possible. Any ideas?
    – Clayton
    Commented Nov 15, 2019 at 16:56
  • You can add an iferror for each cell, but frankly I wouldn't bother. The answer by @marikamitsos is far, far better.
    – Tedinoz
    Commented Nov 17, 2019 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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