Monday, January 31, 2011

yogi_Computing Conditional Array Of Values

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




Cost is to be computed for selected items

formula in cell F2: 
=ArrayFormula(if(C2:C,mmult((A2:A=transpose(A2:A))*(A2:A="x")*(C2:C+D2:D),sign(row(A2:A)))/countif(A2:A,"x"),iferror(1/0)))

yogi_Compute Unique Values Array From Multi-Column Dataset

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




Range A2:D4 ... Dataset

F2: =ArrayFormula(unique(transpose(split(concatenate(A2:D4&" ")," "))))