Transcript In this video, we’ll look at a basic example of sorting with the SORT function . Sorting with formulas is one of those traditionally hard problems in Excel that new dynamic array formulas have made much easier. In this worksheet, we have a list of names, scores, and groups. Currently the data is not sorted. Our goal is to sort this data in descending order by score. There are two new functions in Excel for sorting: SORT and SORTBY ....
Explanation The FV function calculates compound interest and returns the future value of an investment over a specified term. To configure the function, we need to provide a rate, the number of periods, the periodic payment, and the present value: Present value ( pv ) is the named range G4 Rate is provided as annual rate/periods, or rate /C5 Number of periods (nper) is given as periods * term, or C5 * term There is no periodic payment, so we use zero (0) By convention, the present value (pv) is input as a negative value, since the $1000 “leaves your wallet” and goes to the bank during the term....
Purpose Return value Syntax =COUPNUM(settlement,maturity,frequency,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. frequency - Number of coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the COUPNUM function Historically, bonds were printed on paper with detachable coupons. The coupons were presented to the bond issuer by the bondholder to collect periodic interest payments....
Purpose Return value Syntax =EDATE(start_date,months) start_date - Start date as a valid Excel date. months - Number of months before or after start_date. Using the EDATE function The EDATE function returns a date on the same day of the month, n months before or after a start date. You can use EDATE to generate expiration dates, contract dates, due dates, anniversary dates, retirement dates, and other dates that derive from a start date....
Transcript In this video, we’ll look at some formulas you can use to query a table. Because tables support structured references, you can learn a lot about a table with basic formulas. On this sheet, Table1 contains employee data. Let’s run through some examples. To start off, you can use the ROWS function to count table rows. This is the count of data rows only. You can see we have 19 people in the list....
Explanation The first part of the solution uses the MIN and TODAY functions to find the “next date” based on the date today. This is done by filtering the dates through the IF function: IF((date>=TODAY()),date) The logical test generates an array of TRUE / FALSE values, where TRUE corresponds to dates greater than or equal to today: {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} When a result is TRUE, the date is passed into array returned by IF....
Transcript In addition to inserting and deleting rows and columns, you can also insert and delete individual cells. When you insert cells, you have the option to shift the cells down or to the right. When you delete cells, you have the option to shift cells up or to the left. Let’s take a look. To insert a cell horizontally, select the cell to the right of where you want the new cell....
Transcript Excel provides a lot of great tools for working with data. One of those tools is Sort. In this lesson we’ll look at how to quickly sort a list or table using a single column. Let’s take a look. The easiest way to sort in Excel is to use the Sort commands on the Data tab of the ribbon, in the Sort and Filter group. Here you’ll find three basic options: two quick sort buttons—one for sorting in ascending order, and one for sorting in descending order—and a large button for custom sorting....
Transcript The most powerful and flexible way to apply Conditional Formatting is with a formula. Using a formula allows you to check more conditions, and check more than one condition at the same time. Let’s take a look. Here we have the table of random numbers. Let’s build a conditional format that uses a formula to highlight cells greater than a certain value. Start by selecting all values in the table....
Purpose Return value Syntax =IMSQRT(inumber) inumber - A string representing a complex number. Using the IMSQRT function The Excel IMSQRT function returns the square root of a complex number. For example: =IMSQRT("3+4i") // returns "2+i" 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. Basic Example The IMSQRT function returns the complex number’s principal square root (the one with a non-negative real part)....
Pivot tables have a built-in feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to count colors per month for data that covers a 6-month period. The count displayed represents the number of records per month for each color. Fields The source data contains three fields: Date , Sales , and Color . Only two fields are used to create the pivot table: Date and Color ....
To create a pivot table that shows the last 7 days of data (or, more generally, the last n days) you can add a helper column to the source data with a formula to flag records in the last 7 days, then use the helper column to filter the data in the pivot table. In the example shown, the current date is August 16, 2019, and the pivot table shows the seven days previous....
What is regex? A brief history of regex in Excel Regex vs. Excel wildcards Why regex is useful in Excel The REGEXTEST function The REGEXEXTRACT function The REGEXREPLACE function Regex quick reference Important regex terminology Regex anchors Regex tips Summary What is regex? What is regex? Regex, short for Regular Expressions, is a powerful tool for pattern matching in text data. Using a combination of metacharacters, literal characters, character classes, and quantifiers, you can define complex search patterns to extract, validate, or manipulate text data....
About This Shortcut This shortcut will repeat the last action again, when available. For example if you just applied a border and red fill to one cell, you can use this shortcut to apply the same formatting to one or more selected cells. On Windows, you can also use Control + Y for this shortcut. You can add the “Repeat” command to the Quick Access Toolbar to see when an action can be repeated — when it’s possible to repeat an action, the command will highlight and become available....
Explanation In this example, the goal is to repeat a range of values. This can be done in various ways in Excel, but I think the CHOOSEROWS/ CHOOSECOLS functions are the easiest way to retrieve values from the range for now. Both functions work natively with two-dimensional ranges and can accept a single array of numeric index numbers. The formulas below work in two steps: Generate a repeating list of numeric index numbers....
Explanation The goal is to generate a dynamic list of sequential working days with a formula. The start date should be a variable input, and weekends and holidays should be automatically excluded from the output. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the WORKDAY.INTL function like this: =WORKDAY.INTL(B5-1,SEQUENCE(12),1,B8:B11) The result is a list of 12 working days, starting on September 1, 2023, and ending on September 19, 2023....
Transcript In this video, we’ll look at the syntax used in a variety of structured references. Structured references are used to refer to Excel tables in formulas. The syntax for structured references allows you to precisely target different parts of the table. Let’s walk through some examples. All these formulas refer to Table1 at the left. Since the formulas are outside the table, they all include the table name. The formulas in column I are actually entered as text....
Explanation The goal is to calculate a tax of 6% on amounts up to 20,000 and a tax of 10% on amounts of 20,000 or greater. This problem illustrates how to use the IF function to return different calculations. At the core, this formula uses a single IF function . The logical test is based on this expression: B5<=limit When B5 (the current amount) is less than the limit in cell F6 (20,000), the test returns TRUE and the IF function applies rate1 only:...
Explanation Although FILTER is commonly used to filter rows, you can also filter columns. The trick is to use an include argument that operates on columns instead of rows. In this example, we use a hard-coded array constant to filter out unwanted columns, but you can also use a logical expression that returns the same kind of array in a dynamic way. Note: the named range data is used for convenience only; a regular range reference will also work fine....
Purpose Return value Syntax =UPPER(text) text - The text to convert to uppercase. Using the UPPER function The UPPER function converts a text string to all uppercase letters. UPPER function takes just one argument, text , which can be a text value or cell reference. UPPER changes any lowercase characters in the text value to uppercase. Numbers, punctuation, and spaces are not affected. UPPER will convert numbers to text with number formatting removed....
