Transcript Sometimes you have a formula that isn’t finished and Excel won’t let you save the formula. In this video, we’ll look at a simple way to save an unfinished formula so that you don’t lose your work. Sometimes you might be working on a formula that’s broken or unfinished and Excel won’t let you enter it as is. In this example, I’m working with an INDEX MATCH formula that has a problem....
Transcript Now that we’ve looked at the basics of relative references in formulas, let’s look at a common use case for relative references—calculating a total price from a unit price and quantity across a number of line items. Let’s take a look. In this worksheet, we have a list of materials to build a dog house in column B, and the cost of each item in column D. We also have the quantity needed for each item in column C....
Purpose Return value Syntax =IMAGINARY(inumber) inumber - The string representing a complex number. Using the IMAGINARY function The Excel IMAGINARY function returns the imaginary part of a complex number. For example: =IMAGINARY("4+3i") // returns 3 Excel handles complex numbers as strings formatted like “x+yi” or “x+yj”. Use the COMPLEX function to get the string representing a complex number. Explanation A complex number is drawn as an arrow in the complex plane, where the horizontal axis corresponds to the real part of the number and the vertical axis corresponds to the imaginary part of the number....
Purpose Return value Syntax =IMPOWER(inumber,number) inumber - A complex number. number - Power to raise number. Using the IMPOWER function The Excel IMPOWER function returns a complex number raised to a given power. The complex number is input as text, and must be in the form x + yi or x + yj. For example: =IMPOWER("1+2i",2) // returns "-3+4i" In the example shown, the formula in D6, copied down, is:...
Explanation When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range with multiple rows, the ROW function will return an array that contains all row numbers for the range: {5;6;7;8;9;10} To get the first row number in a range, we use the MIN function like this: MIN(ROW(data)) which returns the lowest number in the array, 5. Once we have the first row, we can add the total rows in the range and then subtract 1 to get the last row number....
Explanation At a high level, this formula uses a nested IF function to return an array of holidays between two dates. This array is then processed by the TEXTJOIN function, which converts the array to text using a comma as the delimiter. Working from the inside out, we generate the array of matching holidays using a nested IF: IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,""),"") If the dates in B4:B12 are greater than or equal the start date in F5, and if the dates in B4:B12 are less than or equal the end date in F6, then IF returns a an array of holidays....
Explanation The core of this formula uses the INDEX and MATCH function in a special way to return a full column instead of a single value. Working from the inside out, the MATCH function is used to find the correct column number for the fruit in I6: MATCH(I6,C4:F4,0) MATCH return 2 inside the INDEX function as the column_num argument, where the array is set to the range C5:F11, which includes data for all fruits....
Explanation In this example, the goal is to mark or flag certain records in a data set based on one or more logical conditions . In each case, the result should be “Y” for “Yes” or “N” for “No”. The data represents drinks purchased by five people on different days. Note that most people appear more than once in the data. For each name, we are checking for three separate conditions:...
Explanation This formula depends on a TRUE or FALSE result from a logical test, where FALSE represents the value you are looking for. In the example, the logical test is data=“red”, entered as the lookup_array argument in the MATCH function: =MATCH(FALSE,data="red",0) Once the test is run, it returns an array or TRUE and FALSE values: =MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},0) With the lookup_value set to FALSE, and match_type set to zero to force and exact match, the MATCH function returns 4, the position of the first FALSE in the array....
About This Shortcut Moves current selection one above the active selection. About This Shortcut Moves current selection one cell below the active selection.
Explanation At the core, this formula is a basic INDEX and MATCH formula . However, instead of using the older MATCH function , we are using XMATCH function , which provides a more powerful match mode setting: =INDEX(location,XMATCH(0,distance,1)) Working from the inside out, we are using the XMATCH function to find the position of the nearest location: XMATCH(0,distance,1) // find row nearest zero We do that by setting lookup value to zero (0), lookup array to the distance (C5:C12), and match mode to 1....
Purpose Return value Syntax =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) start_date - The start date. end_date - The end date. weekend - [optional] Setting for which days of the week should be considered weekends. holidays - [optional] A reference to dates that should be considered non-work days. Using the NETWORKDAYS.INTL function The NETWORKDAYS.INTL function returns the number of working days between two dates, taking into account holidays and weekends. This function is more robust than the NETWORKDAYS function because it allows you to control which days of the week are considered weekends....
Explanation The formula shown in this example uses a series of nested SUBSTITUTE functions to strip out parentheses, hyphens, colons, semi-colons, exclamation marks, commas, and periods. The process runs from the inside out, with each SUBSTITUTE replacing one character with a single space, then handing off to the next SUBSTITUTE. The inner most SUBSTITUTE removes the left parentheses, and the result is handed to the next SUBSTITUTE, which removes the right parentheses, and so on....
This chart shows quarterly sales, broken down by quarter into four regions that are stacked, one on top of the other. Stacked column charts can work well when the number of data series and categories is limited. This chart also shows how to use a custom number format ([>=1000]#,##0,“K”;0) to show values in thousands. The data used to plot this chart is shown below: How to make this chart Select the data and insert a column chart: Select the stacked column option Initial chart: Switch rows and columns to group data by quarter: After switching rows and columns: Move legend to top: Add data labels: Select data labels, fill white, then apply custom number format for thousands: Select axis and apply same number format: Set series overlap and gap width: Final stacked column chart: At this point, you can finalize the chart by setting a title, and adjusting the overall chart size and font size for text objects....
Purpose Return value Syntax =RECEIVED(settlement,maturity,investment,discount,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. investment - Amount investment in the security. discount - The security’s discount rate. basis - [optional] Day count basis (see below, default =0). Using the RECEIVED function The RECEIVED function returns the amount received at maturity for a fully invested security. A fully invested security does not pay periodic interest before maturity....
Explanation 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 end with a double slash ("//"). In the current version of Excel, the easiest way to do this is with the TEXTAFTER function. In older versions of Excel, you can use a formula based on the MID and FIND functions....
Transcript In this video, we’ll look at shortcuts available for navigating dialog boxes on Windows. Excel has many dialog boxes that you will use frequently. These includes things like Format Cells, dialog boxes for find and replace, spelling, paste special, and many more. All of these dialogs support common features that let you drive them from the keyboard. To illustrate, we’ll look at the Format Cells dialog, which provides many options in a tabbed interface....
Transcript In this video, we’ll review shortcuts and commands for Paste Special. As you might already know, Paste special is a gateway to many powerful operations in Excel. To use Paste Special, just copy normally, then use the shortcut Ctrl + Alt + V in Windows, Ctrl + Command + V on the Mac. Using this shortcut doesn’t actually finish the Paste, it simply displays the Paste Special dialog, where you can choose which options you want....
Transcript In this video, we’ll look at the shortcuts for undo, redo, and repeat. In this worksheet, we have a set of data without formatting. To illustrate how undo and redo work, I’ll make some changes to the worksheet. But first, notice that I’ve set up the Quick Access Toolbar to show the Undo, Redo, and Repeat commands. This will make it easier to see and understand the history of changes....
Transcript In this video, we’ll take a look at how to handle numbers with the TEXTSPLIT function. One result of using the TEXTSPLIT function is that all output is text, and this can cause problems if you need numeric values. Let me illustrate with an example. In this worksheet, we have some comma-separated text in column B and the goal is to split this text into the 4 columns to the right with the TEXTSPLIT function....
