The goal is to do something if a cell contains one substring or another. Most users will think first of the IF function. However, one problem with IF is that it does not support wildcards like "?" and "*". This means we can't use IF by itself...Read more
This formula uses two named ranges: things, and results. If you are porting this formula directly, be sure to use named ranges with the same names (defined based on your data). If you don't want to use named ranges, use...Read more
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