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.