In this example, the goal is to use VLOOKUP to find and retrieve price information for a given product stored in an external Excel workbook. The workbook exists in the same directory and the data in the file looks like this:
...Read more
In this example, the goal is to create a lookup formula with a variable sheet name. In other words, a formula that uses a sheet name typed into a cell to construct a reference to a range on that sheet. If the sheet name is changed, the reference should update automatically. The key to the...Read more
In this example, the goal is to split a text string at the underscore("_") character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of the underscore character first...Read more
Starting from the inside out, the MID function is used to extract all text after "@":
MID(B5,FIND("@",B5),LEN(B5))
The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text....Read more
In the worksheet shown, we have a list of values in column C. The goal is to dynamically average the last n values using the numbers in cell E5 for n. Since the list may grow over time, the key requirement is to average amounts by position....Read more
In this example, the goal is to count ages in column C according to the brackets defined in columns E and F. All data is in an Excel Table named data defined in the range B5:C16. A simple way to solve this problem is with the COUNTIFS...Read more
The value in G5 is hard-coded. The formula picks up the value in G5, then subtracts the value (if any) in E6 and adds the value (if any) in F6. When the credit or debit values are empty, they behave like zero and have no effect on the result.
When this formula is copied down column G, it...Read more
The TEXT function applies the number format you specify to a numeric value, and returns a result as text.
In this case, the number format provided is "yyyymm", which joins a 4-digit year with a 2-digit month value.
If you only want to display...Read more
In this example, the goal is to convert the text values seen in column B to the numeric values seen in column D. There are several ways to fix this problem in Excel, but this article focuses on a formula-based approach to convert text values to numbers. It also explains how to...Read more
The key to this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format "d". With a hardcoded date in D4, you can use =D4+1 to populate the calendar. This allows you to set up a conditional formatting rule that compares the date in...Read more