Array functions in reports
Formulas start with Q.. They read the batch that is open. A result that has many values spills into the cells below or to the right. An empty list spills one blank cell.
When a sample, compound, or field argument is itself a spill, each element is resolved on its own.
Lists from the method
These take no arguments and spill one column, in method order. If no method is loaded, the cell is blank.
| Formula | Returns |
|---|---|
=Q.Analytes() |
Analyte names. |
=Q.Doses() |
Dose names, including blanks. |
=Q.AcqKeys() |
Acquisition-set names. The default is 1. |
=Q.Quants() |
Distinct quant keys. An empty key is included. |
=Q.SampleTypes() |
Sample-type names. |
=Q.AnalyteGroups() |
Analyte-group names. |
=Q.SumGroups() |
Sum-group names. |
=Q.AnalyteTypes() |
Analyte-type names (Target, ISTD, Surrogate). |
=Q.SampleGroups() |
Sample-group names. |
Samples, compounds, and keys
=Q.Samples([sort], [field, operator, value, …])
Spills sample relative paths. Example: =Q.Samples("Acquired", "Type", "=", "Sample") lists paths whose type is Sample, sorted by acquisition time.
=Q.SmpKeys
Workbook name (no parentheses). Distinct sample keys in batch order.
=Q.Compounds([sort], [sample], [field, operator, value, …])
Compound names for a sample. Example: =Q.Compounds(, Q.CurrentSample, "Result.Concentration", ">", 0).
=Q.AllCompounds([columns], [sort], [field, operator, value, …])
One row per sample and compound. The default columns are Sample.RelativePath,Analyte.Name. Example: =Q.AllCompounds(,, "Result.Concentration", ">", 50).
=Q.Sample(field, [sample])
One sample field. Example: =Q.Sample("SampleName", Q.CurrentSample).
=Q.Compound(propertyPath, [sample], [compound])
One value, or a matrix when an argument spills. Example: =Q.Compound("Result.FConc", Q.CurrentSample, Q.CurrentCompound).
=Q.KeyCompound(propertyPath, sampleKey, [compound])
The chosen result inside a sample key. Example: =Q.KeyCompound("Result.FConc", Q.SmpKeys, Q.CurrentCompound).
=Q.Batch(field) and =Q.Method(field)
Batch or method fields. Empty when nothing is loaded.
Calibration levels
=Q.Levels([sort], [sample], [compound], [field, operator, value, …])
Level keys for a compound.
=Q.Level(field, [sample], [compound], [levelKey])
A level field such as Concentration, Resp, or AccuracyPercent.
=Q.AllLevels(field, [field, operator, value, …])
A matrix of compounds down and level keys across. ANALYTE.NAME or LEVELID returns those labels instead. The first argument is both the value and the sort.
Filters, sort, and other functions
Sort is a field name, or several separated by commas. Add DESC after a field for descending order.
Filters are repeating triplets: field, operator, value. Operators include =, >, >=, <, <=, contains, in, regex, and negated forms such as != and !contains. Prefix the operator with i for a case-insensitive text comparison (i=).
=Q.RepeatCells(source, rowRepeat, columnRepeat) tiles a range. Repeats below 1 are treated as 1.
=Q.ByRowAndColumn(rows, columns, LAMBDA(r, c, …)) evaluates the lambda for every combination. See LAMBDA in reports.
=Q.VSearch(sheets, searchText, returnLocations) finds text (case-insensitive) and returns the sheet name plus cells at R1C1 offsets such as R0C1.
=Q.SheetName() is the sheet that contains the formula. =Q.TemplateName() is the template file name without the extension.
=Q.HideIfError(condition), =Q.HideIfBlank(condition), =Q.HideIfFalse(condition), and =Q.ShowIfTrue(condition) hide rows when the formula is in column A, or columns when it is in row 1. The sheet must contain HIDE in cell A1.
Charts: Q.VSample, Q.VCompound, Q.VCalibration, Q.VQualifier. See Report templates.
Empty and invalid values
No batch or no method gives an empty result for Q.Batch and Q.Method. A filter that matches nothing spills a blank cell. A missing peak or a blank manual integration is blank, not zero. Divide-by-zero and other calculation errors still show as Excel errors, and Hide if error can hide those rows.