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.

yogi_Class Attendance Statistics

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

In sheet AttendanceByGivenDate, forula in cell B2 is:

=transpose(filter(SourceData!A:A,filter(SourceData!A:F,SourceData!A$1:F$1=A2)))

In sheet AttendanceByGivenName, formula in cell B2 is:

=ArrayFormula(if(index(SourceData!B:F,match(A2,SourceData!A:A,0),0),SourceData!B$1:F$1,"No"))

In sheet ClassesByPersonBetween2Dates, formula in cell D2 is:

=count(if(SourceData!B$1:F$1>=B2,if(SourceData!B$1:F$1<=C2,index(SourceData!B:F,match(A2,SourceData!A:A,0),0),"")))

In sheet AbsencesByPersonBetween2Dates, formula in cell D2 is:
=countblank(filter(filter(SourceData!A:F,SourceData!A$1:$F$1>=B2,SourceData!A$1:F$1<=C2),SourceData!A:A=A2))

yogi_Querying Fields With Mixed Data Type -- Be it Numeric Text or Blank

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

Querying data from fields with mixed type data

can become a daunting task -- especially in regard to

what is considered null in dealing with different data types

formula for extracting rows where Col2 is Blank:

=query(index(SourceData!A:C&""),"select* where Col2 ='' ",1)

formula for extracting rows where Col2 is NotBlank:

=query(index(SourceData!A:C&""),"select* where Col2 <>'' ",1)

formula (in row2 -- start of data) for extracting rows where Col2 has NumericData:

=filter(query(SourceData!A2:C,"select* where B!='' ",0),isnumber(index(query(SourceData!A2:C,"select* where B!='' ",0),0,2)+0))

formula for extracting rows where Col2 has TextData:

=query(index(if(isnumber(SourceData!A:C),"",SourceData!A:C)),"select* where Col2<>'' ",1)

yogi_Filtering Form Submitted Data Into Separate Sheet For Each Day

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

Form submitted data is in the sheet BookData

I have created a sheet named 2011-01-01 to filter data for the day 1-Jan-2011 only, and
I have created a sheet named 2011-01-02 to filter data for the day 2-Jan-2011 only

In the day sheets, I have keyed in the day (sheet name) in cell A1

formula in cell A2 to write headers is ...

=ArrayFormula(BookData!A1:D1)

and

formula in cell A3 to filter the data for the day is ...

=filter(BookData!A2:D,int(BookData!A2:A)=A1)

yogi_Return A Value Based On Entries In Other Multiple Cells

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

reference:

http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=42277234&gid=3124035&commentID=31013350&trk=view_disc

Return a value in column D based on entries in columns A, B, and C

If Column A contains a value, "Quoted", 

if Column A an B both contain a value, "Booked" 

If column A, B & C all contain a value, "Invoiced" 

If all 3 columns are blank, the cell should be left blank

formula in cell D2

=ArrayFormula(if(len(A2:A)+len(B2:B)+Len(C2:C)=0,iferror(1/0),iferror(choose(mmult((row(A2:A)=transpose(row(A2:A)))*((A2:A<>"")+(B2:B<>"")+(C2:C<>"")),sign(row(A2:A))),"Quoted","Booked","Invoiced"))))

or

=ArrayFormula(substitute(transpose(split(concatenate(iferror(if(row(A2:A)=transpose(row(A2:A)),if(len(A2:A)+len(B2:B)+len(C2:C),choose(sign(len(A2:A))+sign(len(B2:B))+sign(len(C2:C)),"Quoted","Booked","Invoiced"),"~"),""))&char(9)),char(9))),"~",""))

these formulas should work in Excel as well by making 
adjustment for the Arrayformula

use CSE (Ctrl+Shift+Enter) instead of ArrayFormula wraparound

yogi_Computing Time Worked From ??h??m Start and End Strings

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

If Start Time is blank, End Time is blank or both are blank,
result needs to be blank

Formula in cell C2 ...

=ArrayFormula(if(len(A2:A)*len(B2:B),substitute(text((substitute(substitute(B2:B,"h",":"),"m","")+0)-(substitute(substitute(A2:A,"h",":"),"m","")+0)-"1:00","hh:mm"),":","h")&"m",iferror(1/0)))

yogi_Writing Vertically In Google Spreadsheet

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

UPDATED Feb-07-2011 (added solution using REGEXREPLACE)

Writing in vertical orientation via a command or menu is not supported in Google Spreadsheet; one can however accomplish this using a formula.

So let us say we want to write vertically in cell A1 the text housed in cell E1

then formula in cell A1 ...

=ArrayFormula(concatenate(mid(E1,row(indirect("z1:z"&len(E1))),1)&char(10)))

or using REGEXREPLACE

=regexreplace(A1,".","$1"&char(10))

Now let us write vertically in cell A1 the text housed in cells C1 and D1

then formula in cell E1 is ... = C1&" "&D1

and formula in cell A1 ...

=ArrayFormula(concatenate(mid(E1,row(indirect("z1:z"&len(E1))),1)&char(10)))

or using REGEXREPLACE

=regexreplace(C1&" "&D1,"(.)","$1"&char(10))