Search results

In this example, the goal is to calculate the variance between a Forecast (column C) and Actual (column D) as a percentage. For example, with a Forecast value of 100,000 and an Actual value of 112,000, we want to return a variance of 12%.

The concept of variance requires a baseline value...Read more

In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong...Read more

In this example, the goal is to look up Width and Length based on inputs for Code (K6) and Size (K7). While finding the correct row based on the Code value is straightforward, the problem of how to best retrieve both columns of data (Width and Length) is more challenging. One good way to...Read more

About the #REF! error

The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where...Read more

In this example, we have dates in B5:B16 and sales in C5:C16. Both ranges are named ranges. The goal is to create a dynamic range between two specific dates: the start date in cell F5 and the end date in cell F6. We then...Read more

In this example, the goal is to calculate the total income tax owed in a progressive tax system with multiple tax brackets, as shown in the worksheet. The article below first reviews how taxes are computed in a progressive system. Next, it shows how to accomplish this task in Excel using...Read more

In this example, the goal is to calculate a running total in column D of the worksheet as shown. A running total, or cumulative sum, is a set of partial sums that changes as more data is collected. Each calculation represents the sum of values up to that point. In this example, each...Read more

In this example, the goal is to look up and retrieve an entire row of values in a set of data. For example, when a value like "Neptune" is entered into cell H5, all values in the range C11:F11 should be returned. For convenience and readability, project (B5:B16) and data...Read more

In this example, the goal is to extract the nth word from the text string given in column B. The article below explains two approaches. The first approach is based on the new TEXTSPLIT function, which makes it very...Read more

The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.

In this example, the goal is to generate a list of random 6-character codes. The...Read more

The goal is to generate a series of "custom" days of the week based on a start date entered in cell B5. For example, you might want to list sequential dates for any of the following combinations of days:

• Mondays, Wednesdays, and Fridays (as shown)
...Read more

The general goal is to return the first text value in a range. Specifically, we have dates in column B and some city names in column C. We want a formula to find the first city listed in the range C5:C16. Because some cells in C5:C16 are empty, and some contain zeros, we need to ignore...Read more

In this example, the goal is to count the number of cells in B5:B15 that contain a given number of characters, where the number of characters n is provided as a variable in cell E4. 

SUMPRODUCT with LEN

One way to solve this problem is to use the ...Read more

In this example, the goal is to calculate the time remaining before an expiration date. There are many ways to do something like this in Excel, and in this article, we'll look at three different approaches:

1. Calculating the remaining time in days
2. Calculating the remaining
...Read more

In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach:

1. Define a name called "sheetnames" with an old
...Read more

An interesting problem in Excel is how to look up information related to the maximum value in a set of data. For example, if you have a dataset of property listings and prices, you might want to find details about the property with the highest price. The best way to solve this problem...Read more

With the introduction of Dynamic Arrays in Excel formulas, there is more emphasis on arrays. The #CALC! error occurs when a formula runs into a calculation error with an array. The #CALC! error is a "new" error...Read more

In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns using common values, which in this case are the...Read more

At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with COUNTA like this:

...Read more

About the #DIV/0! error

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!...Read more

In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously ...Read more

This formula relies on the FILTER function to retrieve data based on a logical test built with the COUNTIF function:

=FILTER(list1,COUNTIF(list2,list1))

working from the inside out, the...Read more