Sunday, July 1, 2018

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


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