MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Learn Excel - Replaced Nested IF with VLOOKUP - Podcast 2030
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.
Learn Excel - Replace 12 VLOOKUP with 1 MATCH - Podcast 2028
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...
Learn Excel - Troubleshooting VLOOKUP - Podcast 2027
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
Learn Excel - Array Formulas - Podcast 2026
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...
Learn Excel - Multiple Conditions in IF - Podcast 2025
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
Learn Excel - Workplace Calendar - Podcast 2024
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.
Learn Excel - Calculate Workdays - Podcast 2023
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
Learn Excel - Insert Functions & Loan Payments - Podcast 2022
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...

Forum statistics

Threads
1,226,693
Messages
6,192,471
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top