The FILTER function replicates and extends Excel AutoFilter filtering functionality in formula form. The FILTER function allows us to filter a range of data based on defined criteria. By default, the FILTER function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here. The syntax of the FILTER function is as follows: = FILTER ( array ; include ; [if_empty] ) …
Tag: COUNT function
ROW & COLUMN functions
In Excel, there are specific use cases where an exact row (column) number, or an exact number of rows (columns), are necessary for calculations. The ROW function returns the row number of a reference. The ROW function syntax is as follows: = ROW ( reference ) If reference is omitted, the number of the row in which the formula appears is returned. The COLUMN function returns the column number of a reference. The COLUMN function syntax is as …
INDIRECT function
The INDIRECT function returns the reference specified by a text string. The INDIRECT function syntax is as follows: = INDIRECT ( reference_text ; reference_style ) Reference text can be any text that the function can “convert” to a cell reference. Reference style represents the style of referencing cells in Excel. If omitted or TRUE, the reference text is interpreted as an A1 style reference, i.e., the default style of referencing cells in Excel. If FALSE, the reference text is interpreted …
SMALL & LARGE functions
The SMALL function returns the k-th smallest number from an array of numbers. The SMALL function syntax is as follows: = SMALL ( array; k ) Array can be any row, column, or a combination of both. k is an integer number representing the position of a number in the array if data in that array would be sorted from the smallest. Consider the following example: If k is 1, the smallest number will be returned. If k is …
Conditional calculations
IF functions for cell ranges Using conditional statements, i.e., the IF function, we can test conditions and perform actions if conditions are met. This IF-THEN-ELSE conditional processing is useful when we want to add something new to our rows. However, it is not really appropriate for retrieving data from or about whole ranges of cells, sometimes containing thousands of rows and columns. For example, in order to sum the incentive paid out to “green” team members, we had to …