Dave Bruns

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

You can use the RANK function to rank numeric values. RANK has two modes of operation: ranking values so that the largest value is ranked #1 (order = 0), and ranking values so that the smallest value is #1 (order = 1).

In this case, we are ranking race times. The lowest/fastest value...Read more

You can use the RANK function to rank numeric values.

RANK has two modes of operation: ranking values where the largest value is #1 (order = 0), and ranking values where the lowest value is #1 (order = 1).

In this case, we are ranking test scores, so the highest value should...Read more

Note: there are many ways to summarize data with COUNTIFS, SUMIFS, etc. This example shows one specific and arbitrary way. Before you go the formula route, consider a pivot table first, since pivot tables are far simpler to set up and do most of the hard...Read more

Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same result. The formula in C5, copied down, is:...Read more

In this example, the goal is to remove the protocol from a list of URLs. To remove the protocol from a URL, we need to remove the first part of the URL. Protocols typically look like this:

• http://
• https://
• sftp://

Notice that all protocols...Read more

In this example, the goal is to calculate a weekly total using the data as shown. Notice each week corresponds to 5 rows of data (Monday-Friday) so we will need to sum values in every 5 rows. To build a range that corresponds to the correct 5 rows for each week, we use the OFFSET...Read more

The goal is to return the first text value in each row, ignoring both blank cells and cells that contain numbers. This problem can be solved using the HLOOKUP function. In newer versions of Excel, you can use the XLOOKUP function, which is a more modern lookup function that can look up...Read more

For this example, we want to calculate cumulative interest over the full term of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up CUMIPMT like this:

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents...Read more