DPRODUCT Function
The Excel DPRODUCT function returns the product of values from a set of records that match criteria. The values to multiply are extracted from a given field in the database, specified as an argument.Read more
The Excel DPRODUCT function returns the product of values from a set of records that match criteria. The values to multiply are extracted from a given field in the database, specified as an argument.Read more
Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords.
The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is...Read more
The term "eta lambda" is short for "eta reduced lambda". Although the name sounds complicated, the intent is actually the reverse. Eta lambda refers to a simpler syntax for passing in custom calculations to newer functions like BYROW, BYCOL, SCAN, REDUCE, etc. that are designed to take advantage...Read more
In the formula, COUNTIFS counts the number of times each value in a cell appears in its "parent" column. By definition, each value must appear at least once, so when the count > 1, the value must be a duplicate. The references are carefully locked so the formula will return true only when...Read more
An aggregate operation is an operation that returns a single value. For example, given a range of data, the following formulas return a single result, even when the data provided contains hundreds or thousands of values.
=MAX(data)
=MIN(data)
=COUNT
...Read more
For each cell in the range, SUBSTITUTE removes all the o's from the text, then LEN calculates the length of the text without o's. This number is then subtracted from the length of the text with o's.
Because we are using SUMPRODUCT, the result of all this calculation is a list of items (...Read more
The Excel RANK.AVG function returns the rank of a number against a list of other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates....Read more
The Excel LINEST function returns statistics for a best fit straight line through supplied x and y values. The values returned by LINEST include slope, intercept, standard error values, and more. To find the best fit of a line to the data, LINEST uses the "least squares" method....Read more
This is a tricky use of N() that allows you to use it as a way to leave in-cell comments. It only works for formulas that return numeric results.
The N function takes a value and returns a number. When given a text value, the N function returns zero.
In this case, the primary...Read more
The Excel IMSUM function returns the sum of two or more complex numbers. The complex numbers must be in the form x + yi or x + yj. Use the COMPLEX function to create a complex number from real and imaginary parts.Read more
In this example, the goal is to count the number of cells in a range that do not contain errors. The best way to solve this problem is to use the SUMPRODUCT function together with the ISERROR function. You can also use the COUNTIF function or COUNTIFS function to exclude specific errors...Read more
In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number (n) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:
...Read more
For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of...Read more
At the core, this formula uses the COUNTIFS function to count dates that are greater than or equal to the first day of the current month, and less than the first day of the next month. The EOMONTH function is used to create both dates...Read more
The Excel FORECAST.ETS.SEASONALITY function returns the length in time of a seasonal pattern based on existing values and a timeline.Read more
One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. This can be done also with INDEX and MATCH, but the syntax is more complex.
In the example shown, we want to retrieve all values associated with...Read more
The Excel COUNT function returns a count of values that are numbers. Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored....Read more
In this example, the goal is to repeat a sequence of numbers. This is a useful way to create repeating sequences of numbers by itself. In addition, this formula is a building block to the more general formula here, which can repeat ranges and...Read more
At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday = 7.
The trick to this formula is assembling...Read more
In this example, the goal is to locate the row and column number for a given value in a two-dimensional array.
To get the row number, the data is compared to the max value, which generates an array of TRUE FALSE results. These are multiplied by the result of ROW (data) which generates...Read more
The Excel SLOPE function returns the slope of a regression line based on known y values and known x values. A regression line is a "best fit" line based on known data points.Read more
The goal of this example is to count rows in the data where the date joined falls between start and end dates (inclusive) and the age also falls into the age ranges seen in column G. The formula is complicated somewhat by the fact that the age range labels are actually text, so we need to...Read more