Month: July 2023

LOOKUP Beats XLOOKUP For Horizontal / Vertical Lookup. Excel Magic Trick 1830

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1828-1832.xlsx
Learn about how to lookup an item in a horizontal range and return an item from a horizontal range using the LOOKUP function.
Topics:
1. (00:00) Introduction
2. (00:43) Lookup an item in a horizontal range and return an item from a horizontal range?
3. (00:58) XLOOKUP
4. (01:02) LOOKUP
5. (01:56) 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

Excel Esports Returns on “ESPN8: The Ocho”! on August 4, 2023

Posted on by Annie D

Mark your calendars for 7 AM (ET), August 4, 2023, as we gear up for an adrenaline-pumping Excel Esports Elimination Battle – a completely new Excel Esports game format will be revealed!

8 Excel enthusiasts will be competing in a fierce 30-minute battle, but every 5 minutes one of them will be eliminated: Andrew Ngai, Isaac Lee, Diarmuid Early, David Brown, Alana Reid, Brittany Deaton, Michael Holmes, and Emilie Williams.

Hosted by Excel rock stars Leila Gharani, Bill Jelen, and Oz du Soleil!

From precision plays to amazing strategies, the Excel world’s finest content is being brought to your screens.

Whether you’re a die-hard Continue reading “Excel Esports Returns on “ESPN8: The Ocho”! on August 4, 2023″ →

LOOKUP Beats XLOOKUP For Single Cell Revenue Calculation. SUMPRODUCT Too! EMT 1829

Posted on by Annie D

Learn about how to create a single cell revenue formula that lookups up all prices and multiplies by all quantities and then adds.
Topics:
1. (00:00) Introduction
2. (00:49) About Revenue Calculation
3. (00:59) Three Step Method
4. (01:27) XLOOKUP & SUMPRODUCT
5. (01:32) LOOKUP & SUMPRODUCT
6. (03:06) Summary
7. (03:21) 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

Unleashing The Power Of OpenAI’s Code Interpreter For Data Analysis — This Changes Everything!

Posted on by Annie D

Dive into the world of data science with OpenAI’s Code Interpreter. This video showcases the power and potential of this innovative tool in performing advanced data analysis tasks. We’ll explore a customer service call dataset, demonstrating how Code Interpreter automates the creation of BI notebooks, performs time series analysis, predictive modeling, and more. Discover how Code Interpreter is democratizing data science, making complex analyses accessible to a wider audience. Join us on this exciting journey into the future of data analysis with OpenAI’s Code Interpreter!

Key Takeaways:

– The video introduces and explores the use of OpenAI’s Code Interpreter for advanced data Continue reading “Unleashing The Power Of OpenAI’s Code Interpreter For Data Analysis — This Changes Everything!” →

Federal Income Tax Calculation: LOOKUP Function Beats VLOOKUP & XLOOKUP! EMT 1828 Part 2

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1828-1832.xlsx
Learn how to calculate federal Income tax using the LOOKUP function..
Topics:
1. (00:00) Introduction
2. (00:10) Progressive Tax Brackets
3. (00:46) Manual Formula
4. (01:32) LOOKUP Function formula
5. (02:33) Summary
6. (02:51) 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

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” →

The MAGIC of Power BI in PowerPoint!

Posted on by Annie D

There were some recent updates to the storytelling experience in PowerPoint with Power BI. Patrick walks through how you can use these to make the most of PowerPoint and Power BI!

About storytelling with Power BI in PowerPoint
https://learn.microsoft.com/power-bi/collaborate-share/service-power-bi-powerpoint-add-in-about

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— <a Continue reading “The MAGIC of Power BI in PowerPoint!” →

Leveraging OneLake Explorer – The OneDrive of Data! (Microsoft Fabric)

Posted on by Annie D

It’s been said that OneLake is the OneDrive of data in Microsoft Fabric! OneLake Explorer makes that even more so. Patrick walks you through how this works and how you can leverage it today!

OneLake File Explorer
https://learn.microsoft.com/fabric/onelake/onelake-file-explorer

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— <a href="https://guyinacube.com" Continue reading “Leveraging OneLake Explorer – The OneDrive of Data! (Microsoft Fabric)” →

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” →