Category: Excel

LOOKUP Function Can Do VLOOKUP or HLOOKUP, But Be Careful! EMT 1828 Part 2

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1828-1832.xlsx
Learn that the LOOKUP function can do vertical lookup or horizontal lookup. Also see warning about vertical tables with more columns than rows.
Topics:
1. (00:00) Introduction
2. (00:27) LOOKUP Function Can Do VLOOKUP or HLOOKUP
3. (01:04) LOOKUP to do Vertical Lookup, where rows greater than columns
4. (01:22) LOOKUP to do Horizontal Lookup, where columns greater than rows
5. (01:37) LOOKUP to do Vertical Lookup, where rows equal columns
6. (01:47) Error if you try to do Vertical Lookup when columns greater than rows
7. (02:10) What to do if columns greater than rows
8. (02:37) Summary
9. (02:56) Closing, Video Links

#excel Continue reading “LOOKUP Function Can Do VLOOKUP or HLOOKUP, But Be Careful! EMT 1828 Part 2” →

LOOKUP Beats XLOOKUP at Approximate Match for Discounts, Taxes, Commissions. EMT 1828

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1828-1832.xlsx
Learn about how it is faster to create approximate match lookup formulas with LOOKUP than it is with XLOOKUP when you have discount, tax or commission lookup tables.
Topics:
1. (00:00) Introduction
2. (00:50) Approximate Match Lookup
3. (01:05) XLOOKUP
4. (01:28) LOOKUP
5. (02:22) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula

Leagues Cup Tie Probability / Will Messi Play in Orlando in August – Excel episode 2610

Posted on by Annie D

Microsoft Excel Tutorial: Tiebreakers in Leagues Cup Soccer Group Stage.

Lionel Messi’s debut in the US for Miami is in the Leagues Cup. This four-week tournament includes 47 clubs from across the USA, Canada, and Mexico. What are the odds of a tiebreaker in group play for the Leagues Cup? If Miami finishes first or second in their group, who will they play in the knockout stage?

To download this workbook: https://www.mrexcel.com/youtube/M6xbdRFASnI/

Table of Contents
(0:00) Problem Statement: Odds of tiebreaker
(0:21) Orlando City Soccer two home matches in Leagues Cup
(0:48) Lionel Messi’s US debut will be in Leagues Cup
(1:00) Leagues Continue reading “Leagues Cup Tie Probability / Will Messi Play in Orlando in August – Excel episode 2610” →

3 Power Query Formulas: Hours Worked for Day & Night Shift from Time Values. Excel Magic Trick 1827

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1826-1827.xlsx
Learn about how to calculate hours worked from time values in Power Query. Learn about the importance of M Code Values and Data Types when creating formulas. See three different formulas.
Topics:
1. (00:00) Introduction.
2. (00:21) How Power Query Formulas are different than in the Worksheet and DAX formulas.
3. (01:55) Hours Worked From Time Values for Day Shift using Duration.TotalHours function.
4. (03:13) Hours Worked From Time Values for Night Shift using let expression, the Number.From Function and Number.RoundDown function.
5. (06:45) Hours Worked From Time Values for Night Shift using the Number.From Function and a Logical Test.
6. Continue reading “3 Power Query Formulas: Hours Worked for Day & Night Shift from Time Values. Excel Magic Trick 1827” →

Formula to Calculate Hours Worked in Excel & DAX: Day or Night Shift! Excel Magic Trick 1826

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1826-1827.xlsx
Learn how to take time values and calculate hours worked for day or night shift with Excel formulas or DAX Formulas. Lean about the famous MOD function to make this calculation easy. Learn how the MOD function algorithm works.
Topics:
1. (00:00) Introduction
2. (00:28) Time as proportion of 24 hours.
3. (01:20) Calculate hours work when there is no nightshift.
4. (01:48) Formula #1: Logical Formula. Calculate hours work when there are some who work past midnight, or the nightshift.
5. (03:32) Formula #2: MOD Function. Calculate hours work when there are some who work past midnight, or the Continue reading “Formula to Calculate Hours Worked in Excel & DAX: Day or Night Shift! Excel Magic Trick 1826” →

Embedding Excel Range in a Word Document – Strategic Finance Magazine July 2023

Posted on by Annie D

How to create a live link from an Excel workbook to a Word document.
This video is to accompany the article in the July 2023 issue of Strategic Finance Magazine: https://www.sfmagazine.com/articles/2023/july/excel-embedding-a-range-in-a-word-document

Excel Improvements To Picture In Cell – 2608

Posted on by Annie D

Microsoft Excel Tutorial: Adding Filterable Text to a Picture in Cell

The new Picture in Cell feature would not work with the Filter drop-downs. A new feature allows you to add text to each picture using the Alt Text panel. This Alt Text will appear in the Filter drop-downs, in XLOOKUP results, in the Pivot Table Filter, and in Pivot Table Slicers.

To download this workbook: https://www.mrexcel.com/youtube/xZxKhm9_Xkk/

Table of Contents
(0:00) Problem Statement: Filtering by Picture
(0:53) Use Alt Text to add Text to Picture in cell
(1:17) Alt Text appears in Filter drop-down
(1:44) Getting Picture from XLOOKUP
(2:15) Use Image in Continue reading “Excel Improvements To Picture In Cell – 2608” →

Power Query Dates Are Better Than Excel Worksheet Dates! Excel Magic Trick 1825

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1825.xlsx
Learn about how Power Query Dates are different from Excel Worksheet Dates. Learn that dates in Power Query range from 1/1/0001 to 12/31/9999. Learn how to add and subtract dates in Power Query, Excel too.
Topics:
1. (00:00) Introduction
2. (00:16) Excel & Lotus 1-2-3 Dates have two problems. Power Query solved these problems.
3. (00:53) Date Serial Numbers are Different in Power Query.
4. (01:47) Date Data Type
5. (02:08) Extract Year from Date
6. (02:20) Subtract Two Dates using Duration.Days function.
7. (02:57) Add Days to a Date using Date.AddDays function
8. (03:36) Adding and Subtracting Dates in the Excel Worksheet
9. Continue reading “Power Query Dates Are Better Than Excel Worksheet Dates! Excel Magic Trick 1825” →

Excel Formula to Take a Random Sample: 4 Methods. Excel Magic Trick 1824

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1824.xlsx
Learn how to take a random sample from a population based on a uniform distribution. See 4 Methods. 2 that work in any version of Excel and 2 that work in M 365 Excel.
Topics:
1. (00:00) Introduction
2. (00:14) RAND and RANDARRAY functions create random numbers based on a uniform distribution.
3. (00:31) M 365 Excel Formula #1: TAKE, SORTBY, RANDARRAY Functions.
4. (02:26) Formula for any version of Excel #1: SMALL, RAND, MATCH, INDEX, ROWS, IF and ROWS functions.
5. (05:30) M 365 Excel Formula #1: XMATCH, SMALL, RAND, INDEX, IF and ROWS Functions. Also see an array Continue reading “Excel Formula to Take a Random Sample: 4 Methods. Excel Magic Trick 1824” →

Excel Formula To Randomly Lookup Pictures or Any Item. Excel Magic Trick 1823

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1823.xlsx
Learn about how to create a formula to randomly lookup items without repeat. Lookup pictures or any item. See the functions LET, ROWS, RANDARRAY, SORT, INDEX, XMATCH.
Topics:
1. (00:00) Introduction
2. (00:12) Formula to do random lookup with no repeats. See the functions LET, ROWS, RANDARRAY, SORT, INDEX, XMATCH.
3. (02:36) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #sortfunction #indexfunction #matchfunction #XMATCH

Lookup Picture with Excel Formula ā€“ XLOOKUP or FILTER – Excel Magic Trick 1822

Posted on by Annie D

Download Excel File and zipped folder of pictures: https://excelisfun.net/files/EMT1822.zip
Learn about the new M 365 Excel Lookup Picture ā€œPlace In Cellā€ feature with allows you to easily insert pictures and then use the lookup functions XLOOKUP and FILTER to lookup pictures and to use pictures as lookup values.
Topics:
1. (00:00) Introduction
2. (00:22) Lookup picture with XLOOKUP
3. (01:05) Picture as lookup value in XLOOKUP
4. (01:29) Lookup picture with FILTER
5. (02:29) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #xlookup #filter #filterfunction #lookup #picture