Where named ranges are amts (D4:D11), id (I3), and ids (C4:C11). This is an array formula and must be entered with Control + Shift + Enter. The IF function does the work of figuring out which rows contain a match, and the SMALL function returns the nth value from that list. Inside of IF, the logical test is: which yields this array: Note the customer id matches at the 1st and 4th positions, which appear as TRUE....
When you open Excel, you will see this screen that will allow you to either open a new workbook or a previously saved workbook. When you open a new blank workbook in Excel, This is what you’ll see. These are the main areas of an Excel workbook.
Here we have a sample set of data that shows t-shirt sales. You can see we have columns for date, item, color, and amount. So let’s break this data down by color. Now, before we start, I want to mention that Pivot Tables would be an excellent way to summarize this data, but you can certainly use formulas for basic summaries, and that’s what I’m going to demonstrate here. First, I’m going to name the Color and Amount columns in the data....
While there are some existing formulas in Excel that can help you calculate growth rates, for calculating AAGR and CAGR, you would need a little bit of workaround (nothing too complex though). In this tutorial, I will cover how to calculate the Average Annual Growth Rate (AAGR) in Excel. But before I get into how to calculate AAGR in Excel, let me quickly cover what it is and what does it mean....
When you see a date in a cell in Excel, in the back end it is still stored as a number. In some cases, you may not want to show the date and instead show the serial number it represents. For example, instead of showing 31 Dec 2022 in a cell, you may want to show its numerical value, which is 44926. In this tutorial, I will show you some simple ways you can use to convert a date into a serial number in Excel....
Let’s take a look. You can find the list of recently opened workbooks on the File tab, under Recent. Here you’ll see a list of workbooks, with the most recently opened workbooks at the top. If there are files you use frequently, or if you’re working on a project and want to make sure you have quick access to specific files during the project, you can “pin” any file that appears in the list....
Let’s take a look. One way to hide a column is to choose Hide Columns from the Format menu on the home ribbon. Notice that when a column is hidden, its heading is also hidden, so that there is a gap in the column letters that run across the top of the worksheet. To unhide a column, you need to select columns on both sides of the hidden column. Then, choose Unhide Columns from the Format menu....
In Excel, you can create a macro by recording it or by writing code in the VB editor. Once created, you need to run the macro. In this tutorial, I’ll show you different ways to run a macro in Excel. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. How to Run a Macro in Excel For the purpose of this tutorial, let’s say we have a macro named ‘ColorCell’ with the following code: This one line code would fill the cell A1 of the active sheet with red color....
If so, you can save yourself time and trouble by setting a default template for Excel to use each time you create a new workbook. As long as you name the template correctly, and put it in the correct location, Excel will use your custom template to create all new workbooks. Note: biggest challenge with this tip is figuring out the right location for the template file. This can be maddeningly complex, depending on which platform and version of Excel you use....
Did you know you can show, hide, and resize the formula bar? The formula bar is a toolbar that appears above the worksheet and displays the formulas in the cells of the worksheet. It can be helpful to show, hide, expand and collapse the formula bar when you are working on your spreadsheet. This can be essential when you need to view long formulas or when you want more real estate to view your worksheet area....
Each time you click the Evaluate button, Excel will solve the underlined part of the formula and show you the result. Here’s the same worksheet we looked at in a previous video when we talked about debugging formulas using the F9 key. The Age column contains a formula that calculates age from Birthdate, and the Status column uses the IF function to label each person in the list as either an Adult or Minor....
Here’s the commission example we looked at earlier which uses VLOOKUP to find approximate matches in a named range called “commission table.” Let’s take a look at how VLOOKUP handles approximate matches in more detail. First, when you’re using VLOOKUP for approximate matches, it’s likely that the lookup value won’t be in the table. That’s why you’re using the approximate match setting to start with. In that case, Excel moves through the lookup values in the table until it reaches a value that’s greater than the lookup value....
Simple calculation such as adding the values in a range of cells or counting the values in reach of cells is something that you would have to do when working with data in Excel. And in some cases, you may have to count only those cells that meet a specific criterion. And you can easily do that with the COUNTIF function in Excel In this tutorial, I will show you how the Excel COUNTIF function works with simple examples add a detailed explanation Let’s first look at the syntax of the COUNTIF function:...
When to use Excel FIND Function Excel FIND function can be used when you want to locate a text string within another text string and find its position. What it Returns It returns a number that represents the starting position of the string you are finding in another string. Syntax =FIND(find_text, within_text, [start_num]) Input Arguments find_text – the text or string that you need to find. within_text – the text within which you want to find the find_text argument....
Let’s take a look. The COUNTIF function counts cells that satisfy a single condition that you supply. It takes two arguments: range and criteria. For example, if I want to count the cells in this range that contain the number “15,” I enter B7:B12 for the range, and “15” for the criteria. Excel then returns “1,” since only one cell contains “15”. If I temporarily enter another “15” that result will change....
The results in column E are decimal values with the percentage number format applied. Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns a new price for each item in the table, based on the percentages shown in column D. Negative percentages Negative percentages will have the effect of decreasing the original price. For example, with -10% in cell D5 (-0....
Note: the new TEXTJOIN function is a better way to solve this problem. See below for more information. This part of the formula is annoyingly manual. To speed things up, copy &" “& to the clipboard before you start. Then follow this pattern: [click] [paste] [click] [paste] [click] [paste] until you get to the last cell reference. It actually goes pretty fast. The result of this concatenation (before TRIM and SUBSTITUTE run) is a string like this: Next, the TRIM function is used to “normalize” all spacing....
This formula splits the text in B5 using a comma (",") as the delimiter. The result is a horizontal array that spills into columns D through H. The delimiter is provided as the second argument to the function, and can be changed to suit the situation. Note: This is an interesting and useful LAMBDA example, but Excel 365 now provides the TEXTSPLIT function, which makes this workaround unnecessary in newer versions of Excel....
Unlike the formula explained here, this formula is not recursive. The formula takes four inputs: text - the incoming text chars - the characters to strip rep - the character to replace stripped characters with keep - strip or preserve chars (FALSE = strip, TRUE = preserve) The keep parameter is a boolean that “flips” the behavior of the function from stripping characters to preserving characters. In a nutshell, the SEQUENCE function is used to generate two arrays: one for the text, one for chars....
Pros Simple presentation; easy to read and create Clean presentation of multiple data series with many data points Good for showing trends over periods of time Can handle positive and negative values Cons Harder to read when lines overlap frequently Line can imply more data than actually available (compared to bar or column chart) …Read more Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
