Explanation The goal is to look up and retrieve employee information in a table that contains unique id values in the first column. The VLOOKUP function is straightforward to use with data in this format, but you can easily use the XLOOKUP function as well. See below for a detailed explanation of both approaches. For convenience, id (H4) and data (B4:E104) are named ranges . VLOOKUP function VLOOKUP is an Excel function to get data from a table organized vertically ....

Purpose Return value Syntax =GROWTH(known_y,[known_x],[new_x],const) known_y - The set of y-values you already know in the relationship y = b*m^x. known_x - [optional] The set of x-values that correspond to known_y. If omitted, known_x is assumed to be {1,2,3,…}. new_x - [optional] The new x-values for which you want GROWTH to return corresponding y-values. If omitted, new_x is assumed to be the same as known_x. const - A logical value that controls whether the exponential curve starts at a calculated base value or at 1....

About This Shortcut Hide columns that intersect the current selection, About This Shortcut Hide rows that intersect the current selection.

Transcript In this video, we’ll look at how to create a radar chart. Radar charts can be used to plot the performance, satisfaction, or other rating data across multiple categories in a single chart. In this worksheet we have rating information for an employee in 6 categories: Technical Skill, Experience, Accomplishments, Personality, Sense of humor, and Work ethic. All categories are rated on the same 5-point scale, where 1 is poor and 5 is excellent....

Transcript In this video we’ll look at how to combine the FIND function with the LEFT function to extract the first name from a full name. Let’s take a look. Excel’s LEFT and RIGHT function s are easy to use when you know how many characters you want to extract. But what if you want to extract the first name from a full name? If you try LEFT by itself, you’ll quickly realize you have a problem because you don’t know how many characters to extract....

Transcript Pivot tables are very good at grouping dated information. You can group by year, by month, by quarter, and even by day and hour. But if you want to group by something like day of week, you’ll need to do a little prep work in the source data first. Let’s take a look. For convenience, I’ll first convert the data into a table. This will speed things up later, when we start working with the source data....

Transcript In this lesson, we’ll look at how to name and rename worksheets. We’ll also look at how to change the color of worksheet tabs. Let’s take a look. When you open a new workbook, or when you add new worksheets to an existing workbook, Excel uses a generic name for each sheet: Sheet1, Sheet2, Sheet3, and so on. As you build out a workbook to meet your needs, you’ll probably want to rename these sheets to keep things organized....

Explanation The goal is to sum the quantity for rows where the color is “Red”, the region is “East”, and the quantity is greater than 7. Although there are a number of ways to solve this problem in Excel purpose of this example is to demonstrate how to replace a nested IF with a single IF using Boolean logic. This technique can be used to reduce the complexity of certain formulas....

Purpose Return value Syntax =IMSUB(inumber1,inumber2) inumber1 - Complex number 1. inumber2 - Complex number 2. Using the IMSUB function The IMSUB function returns the difference between two complex numbers. For example: =IMSUB("4+3i","1+2i") // returns "3+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. Explanation The result of the IMSUB function can be visualized by adding the opposite of the second vector tip-to-tail with the first....

Explanation In this example, the goal is to perform a case-sensitive lookup on the first name in column B, based on a lookup value in cell F6. By default, Excel is not case-sensitive and this applies to standard lookup formulas like VLOOKUP , XLOOKUP , and INDEX and MATCH . These formulas will simply return the first match, ignoring case. For example, the formula in cell G5 is a standard INDEX and MATCH formula that is not case-sensitive:...

Explanation The formula above evaluates something like this: =SUM(INDIRECT("'"&B6&"'!"&C6)) =SUM(INDIRECT("'"&"Sheet1"&"'!"&"data")) =SUM('Sheet1'!data) Once the string is assembled using values in B6 and C6, INDIRECT evaluates and transforms the string into a proper reference. Note you can refer to a named range in a formula without using INDIRECT. For example, the formula in D6 could be written: =SUM('Sheet1'!data) However, if you want to assemble the reference as text, and have Excel treat the text as a reference, you need to use INDIRECT....

Purpose Return value Syntax =IRR(values,[guess]) values - Array or reference to cells that contain values. guess - [optional] An estimate for expected IRR. Default is .1 (10%). Using the IRR function The internal rate of return (IRR) is the interest rate received for an investment with payments and income occurring at regular intervals (i.e. monthly, annual). Payments are expressed as negative values and income as positive values. Amounts can vary, but intervals need to be the same....

Purpose Return value Syntax =LEN(text) text - The text for which to calculate length. Using the LEN function The LEN function returns the number of characters in a given text string. LEN takes just one argument, text . LEN counts the number of characters in text , including space and punctuation, and returns a number as the result. If text is an empty string ("") or text is a reference to an empty cell, LEN returns zero....

Purpose Return value Syntax =MMULT(array1,array2) array1 - The first array to multiply. array2 - The second array to multiply. Using the MMULT function The MMULT function returns the matrix product of two arrays, sometimes called the “dot product”. The result from MMULT is an array that contains the same number of rows as array1 and the same number of columns as array2 . The MMULT function appears in certain more advanced formulas that need to process multiple rows or columns....

Explanation Working from the inside out, we use the DATEDIF function to calculate how many complete years are between the original anniversary date and the “as of” date, where the as of date is any date after the anniversary date: DATEDIF(B5,C5,"y") Note: in this case, we are arbitrarily fixing the “as of” date as June 1, 2017 in all examples. Because we are interested in the next anniversary date, we add 1 to the DATEDIF result, then multiply by 12 to convert to years to months....

Explanation In this example, the goal is to build a custom function that will convert a number like 123 into “One hundred twenty three” or “One hundred twenty three dollars” when currency is specified as USD. In addition, the formula should support multiple currencies and handle decimals. Traditionally, “number to words” conversion is handled in Excel with custom VBA code. This is a fine solution, but it does mean the workbook needs to be saved with the ....

Purpose Return value Syntax =PDURATION(rate,pv,fv) rate - Interest rate per period. pv - Present value of the investment. fv - Future value of the investment. Using the PDURATION function The PDURATION function calculates how much time is needed for an initial investment (present value) to reach a given amount (future value), assuming a constant annual interest rate. PDURATION returns an amount of time in periods, which is linked to the number of compounding periods per year....

Explanation In this example, the objective is to calculate a percentage for each goal shown in column C of the table using the actual values in column D. In other words, given a goal of 100000, and an actual amount of 112000, we want to return 112% as the result. The general formula for this calculation, where “x” is the percentage achieved is: x=actual/goal x=112000/100000 x=1.12 Converting this to an Excel formula with cell references, the formula in E5 becomes:...

Explanation In this example, the goal is to assign points based on a ranked score. The scores to rank are in column C, and the calculated Rank is in column D. Points are awarded based on the table in G5:H10. Both tblData (B5:E15) and tblPoints (G5:H10) are Excel Tables created with Control + T . Background study Excel Tables (overview) What is an Excel Table (3 min. video) Introduction to structured references (3 min....

Explanation In this example, the goal is to create a list of pay dates that follow a semimonthly schedule. A semimonthly pay schedule means employees are paid twice a month, usually on fixed dates such as the 1st and 15th or the 15th and the last day of the month. This results in 24 pay periods over the course of a year. For example, in the worksheet shown above, payroll dates are the 1st and 15th of each month....