Category: Excel

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

Generate Letters A to Z in Excel #Short Excel Magic Trick 16

Posted on by Annie D

Learn how to generate the letters A to Z with Excel Worksheet Formulas and Power Query.
Download Excel File: https://excelisfun.net/files/SEMT00016.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel #shortsyoutube #shorts #short
#alphabet #text

Two Date Columns in Fact Table? How To Create Report? DAX, Power Query, Excel? 365 MECS Class 22

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/22-M365ExcelClassVideoFiles.zip
Download pdf notes: https://excelisfun.net/files/22-M365ExcelClassDAX.pdf
This video teaches how to create reports when you have two date columns in a Fact Table Three ways: Worksheet Formulas, DAX Data Model Formulas and Power Query. Learn about how to create a Side By Side Report and a Cross Tabulated report for ordered sales and shipped sales. Learn about USERELATIONSHIP DAX Function, and how to create two date dimension tables using Power Query in the Data Model. See solutions in both Power BI Desktop and Excel Power Pivot.
This full free Microsoft 365 Excel Continue reading “Two Date Columns in Fact Table? How To Create Report? DAX, Power Query, Excel? 365 MECS Class 22” →