Looking to find the average of cells with both letters & numbers **correct forum**

[RJ_B]

I'm trying to find the average number of the maximum speed of 1000+ entries. I'm also trying to find the maximum distance travelled, in a different column, which also contains numbers & letters. Obviously going through 1000 separate cells isn't feasible, and I cannot seem to find the correct formula to do this. I only get errors using base formulas, so require a custom one. How can this be achieved? I'm not sure if this makes sense, but please reply if you require further explanation.

 

[kweaver]

For maximum distance traveled, do you mean the largest value in column A (which shows miles)?

Do you mean this?

One way of two:

=ArrayFormula(max(1*left(A2:A6,len(A2:A6)-3)))

 

[RJ_B]

I mean the absolute total of that "C" column in that example. So it would be 1967 for the total of that example

 

[RJ_B]

To clarify further, if you look at my examples, you'll see that in the first attachment, there is the number, being the miles travelled, and then the letters, being the format. I have over 1000 lines to go through, and I need to find the total amount travelled. My issue here is that SUM, won't work, as there are letters in the cells also. Going through the 1000+ lines to delete the "mi" in each cell, will take way too long and I'm wondering if there's an equation to allow for both numbers and letters to be taken into the calculation.

 

[MARK858]

Going through the 1000+ lines to delete the "mi" in each cell, will take way too long

You don't need to go through each cell you can use Find/Replace just select the whole column, in the Find box type mi and leave the replace with box blank (make sure that the "match entire cell contents" checkbox isn't checked) and click ok.

 

[RJ_B]

I'm honestly so thick, thank you very much

 

[MARK858]

You're welcome (and we all overlook the obvious sometimes)