The goal is to take a specific action when a value begins with "x", "y", or "z". As is often the case in Excel, there are multiple ways to approach this problem. The simplest way is to use the OR function with the LEFT function to create the required logical test. Another option is to...Read more
In this example, the goal is to generate a basic mortgage payment schedule. A mortgage payment schedule is a detailed breakdown of all the payments you will make over the life of a mortgage. It provides a chronological list of each payment, showing the amount that goes toward the principal...Read more
The first thing this formula does is check the date in column D against the date in the header (E4:J4).
=IF($D5<=E$4
Translated: if the date in column D is less than or equal to the date in row E.
Note these are...Read more
In this example, the goal is to highlight all values in K4:M7 (values) that appear in the range B4:I15 (data). The range K4:M7 is named "values" for readability and convenience only. If you don't want to use a named range, use an ...Read more
In this example, the goal is to lookup the first negative value in a set of data. In addition, we also want to get the corresponding date. All data is in an Excel Table called data, in the range B5:C16. This information...Read more
Excel supports both full column, and full row references. A full column reference is a reference that refers to an entire column. For example, to sum all of the values in column A, you can use the SUM function...Read more
The Excel SUMXMY2 function returns the sum of squares of differences between corresponding values in two arrays. Values can be supplied as constants, cell references, or ranges.Read more
In this example, the goal is to display the top 3 values in C5:C16 that match a specific group, entered as a variable in cell F4. If the group is changed, the formulas should calculate new results. For convenience, group (B5:B16) and value (C5:C16) are...Read more
In a nutshell, this formula uses the LARGE function to find the nth largest value in a set of data. Once we have that value, we plug it into a standard INDEX and MATCH formula to retrieve the associated name. In other words, we use the nth largest value...Read more
The Excel WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting on the week that contains January 1. By default, weeks begin on Sunday, but this can be changed.Read more
Note: In Excel 365, the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions.
The core of this formula is a string...Read more
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.Read more
The Excel TEXTSPLIT function splits text by a given delimiter to an array that spills into multiple cells. TEXTSPLIT can split text into rows or columns.Read more
In this example, the goal is to return the fiscal year for any given date, where a fiscal year starts in July as seen in the worksheet. By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019....Read more
The Excel SUMX2PY2 function returns the sum of the difference of squares of corresponding values in two arrays. Values can be supplied as constants, cell references, or ranges.Read more
In this example, the goal is to count the values in column B listed in the range E5:E7. One way to do this is to give the COUNTIF function all three values in the named range things (E5:E7) as criteria, then use the SUMPRODUCT function to get...Read more
The SMALL function retrieves the smallest values from data based on a given rank. For example:
=SMALL(range,1) // smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest
In the...Read more
In this example, the goal is to apply AND and OR logic to an array using the AND function and the OR function. The challenge is that the AND function and the OR function both aggregate...Read more
The SUMPRODUCT function multiplies arrays together and returns the sum of products. The trick is to use simple array expressions to "cancel out" the irrelevant rates in the table. SUMPRODUCT then simply sums the rates that remain.
Working from...Read more
The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.Read more
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0! errors when data is...Read more
In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.
The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not...Read more
The Excel DSUM function returns the sum of values from a set of records that match criteria. The values to sum are extracted from a given field in the database.Read more
In this example, the goal is to count rows in a set of data using multiple criteria and "not equals to" logic. Specifically, we want to count males that are not in group A or B. All data is in an Excel Table named data in the range B5:...Read more