Category: Excel

Excel LAMBDA function – Every Single Things You Ever Wanted To Know – 365 MECS 10

Posted on by Annie D

Download pdf notes: https://excelisfun.net/files/10-M365ExcelClassStart.xlsx
Download Excel file: https://excelisfun.net/files/10-M365ExcelClassFinished.xlsx
This is a 1 hour 45 min. video about everything that the Excel LAMBDA function can do. This video is also a complete lesson in Defined Names, the LET function and Spilled Single Cell Formula Reports.
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (00:52) Overview
3. (02:42) Defined Names
4. (12:24) First look at LAMBDA to create a re-usable function
5. (19:00) Advanced Formula Environment
6. (23:40) Summary of LAMBDA
7. (25:20) Rate Of Change Re-usable LAMBDA function
8. Continue reading “Excel LAMBDA function – Every Single Things You Ever Wanted To Know – 365 MECS 10” →

Excel Sparkling Christmas Tree with Changeable Ornaments with LAMBDA function! EMT 1809

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1809.xlsx
This amazing Christmas Gift is from YouTube Teammate ExcelLambda!! Download the workbook to get your free Christmas Package with many holiday formula surprises!!! Go Team!
Topics:
1. (00:00) Christmas Tree Wishes!
2. (00:23) How to use LAMBDA to make Excel Sparkling Christmas Tree with Changeable Ornaments
3. (01:10) Happy Holidays!

We Wish You A Merry Xmas by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/

Artist: http://audionautix.com/

Excel Formula to Count Max Consecutive Items in a Column -FREQUENCY, IF & MAX functions. EMT 1808

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1806-1808.xlsx
Learn how to create a worksheet formula that counts the max number of occurrences for each unique number in a column using the FREQUENCY, IF, ROW and MAX functions. This solution originally came from Colin Legg at Mr Excel Message Board in 2010.
Topics:
1. (00:00) Introduction
2. (00:05) Review consecutive number tricks
3. (00:25) Create sorted unique list with formula
4. (00:52) Create worksheet formula that counts the max number of occurrences for each unique number in a column using the FREQUENCY, IF, ROW and MAX functions
5. (03:34) Bonus Formula: Spilled MAP and LAMBDA formula
6. () Summary, Closing, Continue reading “Excel Formula to Count Max Consecutive Items in a Column -FREQUENCY, IF & MAX functions. EMT 1808” →

Extract Records with Consecutive Numbers – Power Query Group By GroupKind.Local. EMT 1807

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1806-1808.xlsx
Learn how to use Power Query extract all records for weights that occur consecutively 3 or more times.
Topics:
1. (00:00) Introduction
2. (00:06) Review consecutive number tricks
3. (00:32) Create Names From Selection feature (Ctrl + Shift + F3) to create Defined Name for Extract Records Hurdle
4. (01:04) Import Power Query Input From Excel Worksheet and use in query, Keyboard to import data from worksheet into Power Query: Right-Click, G
5. (02:10) Import Excel Table into Power Query with keyboard
6. (02:24) Group By Feature to group by Consecutive Occurrences using 4th argument in Table.Group Power Query M Code Continue reading “Extract Records with Consecutive Numbers – Power Query Group By GroupKind.Local. EMT 1807” →

Conditional Formatting for Consecutive Numbers with Logical Formula. Excel Magic Trick 1806

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1806-1808.xlsx
Learn how to conditional format a row in a table when there are consecutive numbers in the weight column.
Topics:
1. (00:00) Introduction
2. (00:05) Intro to next three videos about Consecutive Numbers in a Column
3. (00:25) Conditional Format Row for Consecutive Numbers
4. (01:00) Build Logical Formula using OR function
5. (02:02) Mixed Cell References with Column Locked. This allows Conditional Formatting for the row.
6. (02:52) Paste Logical Formula into Conditional Fo0rmatting dialog box
7. (03:38) Summary, Closing, Video Links

Lonely Book Signings And Faster Way To Generate 729 Combinations – 2352

Posted on by Annie D

You’ve probably seen the story of Chelsea Banning in the news lately. 37 people RSVP’d to her book signing and only two showed up. That is DOUBLE the turnout for my Manhattan book signing in 2003. Also in the episode: two faster ways to generate all 729 combinations of the outcome of World Cup Group Match Play.

Thanks to Exceλambda and Kyle F. for faster ways to generate all combinations of 0, 1, and 2 for 6 World Cup Group Stage Matches.

UnPivot Cross Tabulated Table with Power Query or Dynamic Spilled Array Formula? EMT 1805

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1805.xlsx
Learn how to convert a cross tabulated table with row header and column header conditions back into a proper data set using Dynamic Spilled Array Formulas and Power Query.
Topics:
1. (00:00) Introduction
2. (00:16) Old School History Formula
3. (00:54) Three Dynamic Spilled Array Formula using IFNA and TOCOL functions
4. (03:55) One Dynamic Spilled Array Formula using HSTACK, IFNA and TOCOL functions
5. (04:53) One Dynamic Spilled Array Formula using LET, HSTACK, IFNA and TOCOL functions
6. (05:54) Power Query
7. (07:49) Summary, Closing, Video Links

World Cup Group Stage Tiebreaker Odds – 2531

Posted on by Annie D

The World Cup is now in group stage. Each group has four teams. The top two teams advance to the knockout stage. This video explains the 7 tiebreaker steps, and uses Microsoft Excel to analyze the 729 permutations that can happen in any one group. How likely is it that the tiebreaker will need to be used?

Table of Contents

(0:00) World Cup Group Stage Scoring
(1:00) Tiebreaker rules
(1:50) Odds of a tiebreaker
(2:20) Excel Model for one group
(2:50) TEXTSPLIT function
(3:22) Excel Trace Dependents
(3:45) Scoring & Sorting
(4:10) Detecting a Tie
(4:41) Why 729 permutations
(4:53) 28% tiebreaker chance
(5:46) Six draws leads to 12 points
(6:04) 18 points Continue reading “World Cup Group Stage Tiebreaker Odds – 2531” →

Excel Array Formulas & Functions: 50 Examples of How to Become an Array Expert! – 365 MECS 09

Posted on by Annie D

Download Excel file: https://excelisfun.net/files/09-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/09-M365ExcelClass.pdf
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (00:33) Review Topics
3. (01:06) Review of pdf notes!!!!
4. (02:15) Array Formula Fundamentals: 10 Examples and lots of Terms, Definitions, and Types
5. (23:08) Two Main Reasons to use Array Formulas:
6. (23:08) 1) Array Formulas Make Things Easy (Budget and Investment Examples)
7. (27:55) 2) Array Formulas Help Make Compact Solutions (Finance and Budget Examples)
8. (34:26) SUMPRODUCT function (Total Sales and Grading Examples)
9. (38:21) List of ALL Array Continue reading “Excel Array Formulas & Functions: 50 Examples of How to Become an Array Expert! – 365 MECS 09” →

Group Dates In Pivot Table When Blanks – 2530

Posted on by Annie D

Lasma wants her pivot table to group daily dates up to months and years. But she has some empty rows and the Group Field is greyed out.

In this video:
Surprise! Microsoft 365 now allows blanks in the date field and you can still group.
Solution 1: Fill blank date fields with 2199 December 31
Solution 2: Add your own Year and Month fields to the data set
And, a warning from Debra Dalgleish at Contextures: if you mix dates and text, the Group Field will still be greyed out.

Table of Contents
(0:00) Pivot Group Dates with Blanks
(0:15) Fix from Excel Team
(1:10) Select empty date cells
(2:09) Continue reading “Group Dates In Pivot Table When Blanks – 2530” →

Excel Max Minus Min In A Pivot Table – 2529

Posted on by Annie D

Kelly wants to display a Delta in a Pivot Table. Unfortunately, Calculated Fields in a Pivot Table look at each individual row, so the MAX(B2)-MIN(B2) will always be zero.
In this video, a DAX Measure solves the problem. But to unlock DAX, you need to choose Add This Data To The Data Model.

Table of Contents
(0:00) Need Max minus Min in pivot table
(0:31) MIN and MAX are easy
(0:51) Calculated Field fails
(1:51) Add to Data Model
(2:51) New Measure
(4:25) Wrap-up