yogi_QueryFunction -- Aggregating Numeric Data In A Field With Mixed Data Type

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

As shown in the following figure ...

data in Field2 is MIXED Numeric and Text. In the data as shown, there are 4 TEXT entries, 3 Numeric entries, and the rest are BLANK entries. In Google spreadsheet, QUERY function considers the data type of  a FIELD by its predominant data TYPE ... so in this particular case, the data TYPE of Field2 is TEXT.

If I want the QUERY function to operate on the numeric data in Field2, I will need to convert the TEXT entries in Field2 to NUMERIC entries. So, I have created a new set of date to be QUERIed as presented in cells E2:H12, by using the following formula in cell E2 ...

=ArrayFormula({"",A2:A,if(istext(B2:B),0,B2:B),C2:C})

In data set E2:H12, data in column F is all numeric, so it will lend itself to be AGGREGATED and operate on as necessary

We will look at various data extractions in the various sheets of the spreadsheet.