With many complex calculations, especially those involving one or more IF functions (also, the FILTER function and various lookup functions), the same expression will often be repeated multiple times in a formula. This can result in lengthy and hard-to-read formulas and can also cause performance issues, given that the same (often complex) expression can be calculated multiple times. The LET function allows us to assign names to values and/or calculation results, with those names applying in the scope of a …
Tag: MIN function
FILTER function
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] ) …
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 …
SUMIFS criteria: formulas, functions and conditional statements
We’ve previously established the rules for writing criteria in the SUMIFS function: we can reference any cell we can enter any number directly into the function we can enter text directly in the function, nested inside quotation marks “” a logical test is nested inside of quotation marks “”, with the comparison operator coming first and a number second. We will further expand on the topic in this article. Formulas and functions can also be nested in SUMIFS criteria. If needed, …
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 …
Numbers in Excel
How are numbers stored, formatted and calculated? When dealing with numbers in Excel, we should be aware of a few things: numbers in Excel can be formatted in many ways: plain numbers, currency, percentage, date, etc. no matter how they are formatted and possibly edited, underlying values saved in cells are still decimal numbers those decimal numbers that Excel uses and calculates with go up to 15 decimal places (and even with that, there are caveats) this is not changed even …