In this example, the goal is to return the maximum value in a set of data while ignoring any errors that might exist. This problem can be solved with the AGGREGATE function or with the MAXIFS function, as explained below.
The standard way to retrieve the maximum...Read more
In this example, the goal is to average a list of values that may contain errors. The values to average are in the named range data (B5:B15). Normally, you can use the AVERAGE function to...Read more
The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc. performed on one or more references. The AGGREGATE function is like an upgraded version of the older SUBTOTAL function, and provides more...Read more
The YEAR function extracts the year from a valid Excel date. For example:
=YEAR("15-Jun-2021") // returns 2021
In this case, we are giving YEAR and array of dates in...Read more
Criteria are a key concept in Excel, but building useful criteria for text, numbers, dates, times, etc. is hard because it requires a good understanding of how Excel handles data. This guide will help you build formulas that work the first time with over 50 examples.Read more
Although Excel has a RANK function, there is no RANKIF function to perform a conditional rank. However, you can easily create a conditional RANK with the COUNTIFS function.
The COUNTIFS function can perform a conditional count using two or more...Read more
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to...Read more
In this example, the XOR function contains two expressions, one to test for an "x" in column C, and one to test for an "x" in column D.
C5="x" // TRUE if coffee is "x"
D5="x" // TRUE if tea is "x"
With two logical criteria, XOR has a...Read more
Consider a simple dynamic reference to Sheet2 using the INDIRECT in a formula like this:
=INDIRECT($B$5&"!"&"A1")
If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet....Read more
The goal is to retrieve the value of a cell at a given row and column location, which are entered in cells G5 and G4, respectively. There are several ways to go about this, depending on your needs. See below for options.
In the example worksheet, the...Read more