Posts

Explanation The LAMBDA function can be used to create custom, reusable functions in Excel. This example illustrates a feature called recursion, in which a function calls itself. Recursion can be used to create elegant, compact, non-redundant code. However, one disadvantage to recursive LAMBDA functions is that they are not easy to test. This is because they can’t be debugged directly on the worksheet, since a generic (unnamed) LAMBDA does not yet have a name and therefore can’t call itself....

Explanation In this example, the goal is to create a formula that will return TRUE if a date is in the last complete 6 month period, starting in the previous month. This means the date must fall between a calculated start date and end date, which requires two logical tests. The formula uses the AND function to require that both logical tests are TRUE. In the example shown, the current date is October 30, 2021....

Purpose Return value Syntax =LET(name1,value1,[name2/value2],...,result) name1 - First name to assign. Must begin with a letter. value1 - The value or calculation to assign to name 1. name2/value2 - [optional] Second name and value. Entered as a pair of arguments. result - A calculation or a variable previously calculated. Using the LET function The LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....

Explanation The LOOKUP function assumes data is sorted, and always does an approximate match. If the lookup value is greater than all values in the lookup array, default behavior is to “fall back” to the previous value. This formula exploits this behavior by creating an array that contains only 1s and errors, then deliberately looking for the value 2, which will never be found. First, this expression is evaluated: item=F7 When F7 contains “sandals” the result is an array of TRUE and FALSE values like this:...

About This Shortcut Moves current selection one cell below the active selection. About This Shortcut Moves current selection one screen to the right of the active selection. The distance covered depends on the size of the window.

About This Shortcut Moves current selection one screen above the active selection. The distance covered depends on the size of the window. If there are 25 rows visible (of the same height), the active selection will move 25 rows up. About This Shortcut Moves current selection one screen below the active selection. The distance covered depends on the size of the window. If there are 25 rows visible (of the same height), the active selection will move 25 rows down....

Explanation The default behavior of the MATCH function is to match the “next smallest” value in a list that’s sorted in ascending order. Essentially, MATCH moves forward in the list until it encounters a value larger than the lookup value, then drops back to the previous value. So, when lookup values are sorted in ascending order, both of these formulas return “next smallest”: =MATCH(value,array) // default =MATCH(value,array,1) // explicit However, by setting match type to -1, and sorting lookup values in descending order, MATCH will return the next largest match....

Explanation Net Present Value (NPV) is the present value of expected future cash flows minus the initial cost of investment. The NPV function in Excel only calculates the present value of uneven cashflows, so the initial cost must be handled explicitly. One way to calculate Net Present Value in Excel is to use NPV to get the present value of all expected cash flows, then subtract the initial investment. This is the approach taken in the example shown, where the formula in F6 is:...

Purpose Return value Syntax =ODDLPRICE(sd,md,id,rate,yld,redem,freq,[basis]) sd - Settlement date of the security. md - Maturity date of the security. id - Last interest date of security. rate - Interest rate of security. yld - Annual required rate of return. redem - Redemption value per $100 face value. freq - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4). basis - [optional] Day count basis (see below, default =0)....

Pivot tables have a built-in feature to group numbers into buckets at a given interval. In the example shown, a pivot table is used to group a list of 300 names into age brackets separated by 10 years. This numeric grouping is fully automatic. Fields The source data contains three fields: Name, Age, and Group. Only Name and Age are used in the pivot table as shown: Age is used as a Row field....

Pivot tables are an easy way to quickly count unique values in a data set, and can easily be adapted to perform a two-way count. In the example shown above, a pivot table is used to count unique combinations of color and size, based on data in the range B5:D16, defined as an Excel Table . Fields The pivot table shown is based on three fields: Color, Size, and Qty. The Color field is configured as a Rows field, and the Size field is configured as a Columns field....

Purpose Return value Syntax =PPMT(rate,per,nper,pv,[fv],[type]) rate - The interest rate per period. per - The given payment period. nper - The total number of payments for the loan. pv - The present value, or total value of all payments now. fv - [optional] The cash balance desired after last payment is made. Defaults to 0. type - [optional] When payments are due. 0 = end of period. 1 = beginning of period....

Explanation In this example, the goal is to test if a given range contains duplicate values and return TRUE if duplicates exist and FALSE if not. This is essentially a counting problem and the solution is based on the COUNTIF function , which counts values in a range that meet supplied criteria. The formula used in E5 is: =OR(COUNTIF(data,data)>1) where data is the named range B5:B16. Background study Below are related links to help you understand how this formula works:...

Explanation In this example, the goal is to calculate the required recovery rate (or gain) to complete offset a loss expressed as a negative percentage. This is not a difficult problem, but you must pay attention to the sign of the loss and be sure to format the result with the percentage number format . Background One of the most important aspects of investing is the math of gains and losses....

Transcript In this video, we’ll look at shortcuts for creating and applying named ranges. Here we have a set of sales data that spans 2 years, broken down by month. Using named ranges, I’m going to add several formulas to fill in the summary information, based on a name in I8. Generally, the fastest way to create a named range is to use the name box. Just make a selection and type a valid name....

Transcript This video takes a look at the Format Task Pane, a formatting control added in Excel 2013 to format and configure chart elements. The Format Task Pane was added in Excel 2013 and provides detailed controls for most chart elements. Previously, this sort of formatting was done with the Format dialog box. The main idea behind the Format Task pane is to provide a more complete set of controls to format things like charts without obscuring the worksheet below....

Explanation This example uses the UNIQUE function together with the FILTER function. You can see a more basic example here . The trick in this case is to apply criteria to the FILTER function to only allow values based on the count of occurrence. Working from the inside out, this is done with COUNTIF and the FILTER function here: FILTER(data,COUNTIF(data,data)>1) The result from COUNTIF is an array of counts like this:...

Explanation The XMATCH function offers new features not available with the MATCH function. One of these is the ability to perform a “reverse search”, by setting the optional search mode argument. The default value for search mode is 1, which specifies a normal “first to last” search. In this mode, XMATCH will match the lookup value against the lookup array, beginning at the first value. =XMATCH(F5,names,0,1) // start with first name Setting search mode to -1 species a “last to first” search....

Transcript Hey, Dave here from Exceljet. So today I want to do some training on efficiency and entering formulas and functions in Excel, so I’m going to take you through more than 20 different tips for working faster with formulas and functions. And I’ll look at things like how to enter formulas and functions faster, how to work with the function arguments using the Function Tip Window or Screen Tip Window....

About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a bottom border added or removed from the border preview area. On the Mac, this shortcut works directly on the worksheet, and adds a bottom border to each cell in the selection. About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab....