MrExcel Excel Videos

With a tiered commission, bonus, or discount program, you often have to nest your IF functions The Excel 2003 limit was 7 nested IF statements. You can now nest 32, but I don't think you should ever nest 32 When would you ever use the approximate match version of VLOOKUP? This is the time. Translate the discount program into a lookup table VLOOKUP won't find the answer in most cases. Putting ,True at the end will tell VLOOKUP to find the value just less. This is the only time the VLOOKUP table has to be sorted. Don't want the VLOOKUP table off to the side? Embed it in the formula. F2 to edit the formula. Select the lookup table. Press F9. Enter.

VLOOKUP can not look to the left of the key field! Use MATCH to find where the item is located, then use INDEX to retrieve the lookup value. Or - in real life, rearrange the lookup table This episode features an original letterpress poster from Bobby Rosenstock at justajar.com

Say that you have to do 12 columns of VLOOKUP Carefully use a single dollar sign before the column of the lookup value Carefully use four dollar signs for the lookup table You are still hard-coding the third column argument. One common solution is to add a row of helper cells with the column number. Another less-efficient solution is to use COLUMN(B2) inside the VLOOKUP formula. But, doing 12 VLOOKUP for each row is very inefficient Instead, add a helper column with a heading of WHERE and do a single Match. The MATCH takes as long as the VLOOKUP for January. You can then use 12 INDEX functions. These are incredibly fast compared to VLOOKUP. The INDEX will point to a single column of answers with $ before the rows. The INDEX will point...

VLOOKUP solves many problems Common VLOOKUP problems: If VLOOKUP starts out working, but #N/A becomes more prominent: forgot $ in lookup table A few #N/A: items missing from the table None of the VLOOKUP work: check for trailing spaces Remove trailing spaces with TRIM Numbers and numbers stored as text Select both columns and use Alt+DEF Episode includes a joke that both accountants and IT people find funny, but for different reasons

There are a secret class of formulas called Array Formulas. An array formula can do thousands of intermediate calculations. They often require you to press Ctrl+Shift+Enter, but not always. The best book on array formulas is Mike Girvin's Ctrl+Shift+Enter. INDIRECT lets you use concatenation to build something that looks like a cell reference. Dates are nicely formatted but are stored as a number of days since January 1 1900. Concatenating two dates will point to a range of rows in Excel. Asking for the ROW(INDIRECT(Date1:Date2)) will "pop out" an array of many consecutive numbers Using the WEEKDAY function to figure out if a date is Friday. How many Fridays occur in this July? To watch a formula calculate in slow motion, use the...

Three ways to handle multiple conditions in an IF function. Episode recap: The simplest IF function is =IF(Logical Test,Formula if True, Formula if False) But what to you do if you have to test two conditions? Many people will do =IF(Test 1, IF(Test 2, Formula if True, False), False) This gets unwieldy if 3, 5, 17 conditions! Instead, use =IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) If you like AND, consider OR, NOT for other situations NAND can be done with NOT(AND()) NOR can be done with NOT(OR()) Be careful when using XOR as the results are not what you expect

Yesterday in podcast 2023 I used NETWORKDAYS and NETWORKDAYS.INTL There is a similar pair of functions WORKDAY and WORKDAY.INTL This function takes a start date, then a number of days, weekend type and holidays and calculates the end date. For example, calculate when a 30 work-day probation period might end. But a more common use might be to build an employee schedule or employee calendar Put the first start date. Then add 1 workday using WORKDAY or WORKDAY.INTL. Drag that formula down to build the schedule.

Calculate work days between two dates excluding weekends and holidays. This episode also handles 6-day workweeks, countries where the weekend is Friday and Saturday, plus businesses like barber shops or farm markets that have operating hours on Monday, Thursday, Friday, Saturday. Episode Recap: Date math in Excel: Subtract earlier date from later date + 1 To ignore weekends, use NETWORKDAYS function To not count holidays, use the 3rd argument in NETWORKDAYS For non-standard weekends, use NETWORKDAYS.INTL Secret 7-binary digit code for work weeks that are not consecutive days Alt+ESF for Paste Special Formulas

How to discover which Excel calculation function to use. This episode shows how to use the PMT function to calculate a loan payment and how the Function Arguments helps with tricky arguments like dividing the interest rate by 12. Episode recap: There are over 400 calculation functions in Excel. If you don't know what function to use, click the fx button. This button is on the Formulas tab and also to the left of the formula bar. Type what you are trying to do and Search. The results will show you the functions related to your search term. When you click OK, you are taken to the Function Arguments dialog. This dialog provides help for each argument. Bold arguments are required. Once the bold arguments are completed, you will see the...