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][1]][1]


  [1]: https://i.sstatic.net/2gAK8.png

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.