yogi_Clean up a column of data and compute the average of numbers from row 2 down

Google Spreadsheet   Post  #2468

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jul-01-2018

question by: meph2u
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/278SDB2MajE;context-place=mydiscussions
Clean up of a column of data to be able to average it

I have a column of data which I want to average.   It needs cleaning as follows:

Anything with an F or F* becomes 18

Anything with a #* becomes the number

Anything with a # stays a number

Anything with a time becomes a 0

Here is an example:  https://docs.google.com/spreadsheets/d/1cPkMFrYrPti8KC018DHAk_NMwtMGaYCypjst7DW1_N4/edit?usp=sharing

What I am looking for is a single formula to put in K1 that will do the work of the formulas in L2, M2, N2, O2 and O1

L2:  =arrayformula((substitute(K2:K,"*","")))

M2: =arrayformula((substitute(L2:L,"F",18)))

N2: =ARRAYFORMULA(value(M2:M)) - this has a problem as it turns blank cells into 0 which messes up the average in O1. But ISTEXT and ISBLANK don't seem to help. For example =ARRAYFORMULA(if(ISblank(M2:M),"",value(M2:M)))

O2: =arrayformula(if(N2:N<1,0,N2:N)) (all times seem to be less than 1 after the "value" function is applied

O1: =average(O2:O) this would work if all blanks in M had not been converted to "0" with the value function in N2

Any good ideas for this one? Thanks