Dynamic Array Functions in Excel are so fabulous … BUT!
As with anything, there are traps, warnings and caveats. In this video I quickly cover 5 things you should know about Dynamic Array Functions and Formulas.
https://ozdusoleil.com/wp-content/uploads/2019/03/5-dynamic-array-gotchas-in-excel-6187-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-24 21:00:402020-11-03 09:42:195 Dynamic Array Gotchas in Excel
7 new Dynamic Array functions have been added to Excel! In this video you’ll see the FILTER and SORT functions and how 4 formulas bring back results in lots of cells!
Also: – There’s new nomenclature using the # to reference a dynamic range. – The new SPILL error lets you know that your result doesn’t have enough room. So, you need to move something and make room.
https://ozdusoleil.com/wp-content/uploads/2018/10/dynamic-arrays-sort-filter-and-dynamic-vlookup-91-youtube-thumbnail.jpg480640Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-22 21:00:062020-11-03 09:42:20Dynamic Arrays: SORT, FILTER, and Dynamic VLOOKUP
There’s a lot in this video! When you get this working, you’ll be entitled to wear your underwear outside your pants like a true Super Hero!
The challenge: We’d like random names, but also choose which format we’d like our result in: – First Name Last Name – Last Name, First Name – First Name MI Last Name – Last Name, First Name MI
I thought about using a custom function but realized it’d be simpler to drill down (pass a parameter).
In this video you see: – Column by Example – Excel Tables – Power Query – Left Outer Join – M Code – Merge Columns – and EVVYTHANG!
https://ozdusoleil.com/wp-content/uploads/2018/10/pass-parameter-drill-down-to-pick-name-format-with-power-query-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-09 08:53:512020-11-03 09:42:23Pass Parameter (Drill Down) to Pick Name Format with Power Query
Ken Puls of ExcelGuru presented a 3rd Excel Challenge and here’s my solution.
The challenge is to take a word, assign values to each letter and generate a total for each word.
A = 1 a = 1 . . Z = 26 z = 26
The word ‘Attitude’ gets 100 points.
As I started thinking about a solution I started thinking too hard about it. I wanted something more crafty or sophisticated, but then I realized that I saw a path toward success and that’s what mattered.
So, this video is really about understanding your strengths and style and not apologizing. My Excel style is akin to the style of Jerome Bettis, the former runningback for the Pittsburgh Steelers. He was big and powerful … also kinda slow and not elegant.
But what’s the bottom line? The bottom line is to get the job done–however you can responsibly get the job done.
https://ozdusoleil.com/wp-content/uploads/2018/10/excelguru-challenge-3-parsing-text-own-your-excel-style-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-10-02 12:07:392020-11-03 09:42:25ExcelGuru Challenge #3: Parsing Text & Own Your Excel Style
Custom Functions in Excel’s Power Query. It’s finally time to take them on. For a while I couldn’t think of a good use. It’d seen the how-to but never really a what-for.
In this video I propose a common situation where lists will trickle in and we’ll need to convert the state abbreviations into state names via a master list. E.g., convert LA to Louisiana, and GU to Guam.
We’ll get one list, then 2 hours later another list, then tomorrow we’ll get 2 lists. Ongoing trickle. We could write a new VLOOKUP every time, but that would get tedious.
A Custom Function in Power Query will help us out.
I show how to do this as a partially manual process with 2 steps: 1. Manually bring the data into Power Query 2. Apply the custom function.
Pretty simple, but it takes some thinking. You’ll see some M Code and the “Insert Step After” feature.
https://ozdusoleil.com/wp-content/uploads/2018/09/custom-function-in-power-query-to-merge-2-tables-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-09-12 22:05:572020-11-03 09:42:28Custom Function in Power Query to Merge 2 Tables
6 of us Excel MVPs came together to take on the challenge of making an integrated Excel solution by starting with 4 random Excel elements based on your suggestions. We have:
– MAX function – FREQUENCY function – Form Controls – 3D Model
See the other awesome entries from my fellow Excel MVPs in the playlist here: https://www.youtube.com/playlist?list=PLHrPHBbDHgT0UnY1qb36YZrBwpWNJX8Lx
And be sure to subscribe to their channels for more Excel education
– Leila Gharani, This Chick’s Trouble: Excel Hash
– Mr Excel, Bill Jelen, Will Bennu Impact Earth?
– Mike Girvin, ExcelIsFun, Max Consecutive Wins for Best City https://youtu.be/U1h2FrZFMqI – Mynda Treacy, My Online Training Hub, Interactive Bug Menu
– Jon Acampora, Excel Campus https://youtu.be/0ZW-h4kPlBI VOTE for your favorite entry here: https://tinyurl.com/y9hmvelp
My solution is called “Greatest Threat” The model allows users to set a high and low number, and one of 2 situations. The model then calculates the greatest threat. Example:
Situation A, between 8 and 96 Greatest Threat: Wasp de Fatal
Situation B, between 2 and 12 Greatest Threat: Ghost Wolf
Situation B, between 2 and 20 Greatest Threat: Vampire Earwig
https://ozdusoleil.com/wp-content/uploads/2018/09/excelhash-greatest-threat-max-frequency-form-controls-3d-model-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-09-04 10:00:022020-11-03 09:42:32ExcelHash: Greatest Threat – MAX, FREQUENCY, Form Controls, 3D Model
Today I had to formally weigh in on the debate between VLOOKUP and INDEX/MATCH. This argument has gone on for years, and last week I heard strains of it rising up again. So, here’s what I say:
VLOOKUP is a tool. INDEX/MATCH is a tool. VLOOKUP has weaknesses, e.g., it’s easy to break, and it can’t do lookups to the left. Conversely, INDEX/MATCH is undoubtedly more robust. But it’s actually a function nested inside another function. That makes it awkward to work with.
They’re both legitimate choices. The problem comes up with the INDEX/MATCH zealots who call for the Excel developers at Microsoft to eliminate VLOOKUP. They are proud to post things like, “I haven’t used VLOOKUP in 10 years” and insist that VLOOKUP shouldn’t be taught to beginners because it’s a bad habit.
Basically, they’ve turned INDEX/MATCH into a religion … and they’re viciously recruiting new members.
My video makes 3 points: 1. VLOOKUP and INDEX/MATCH are tools. Period. 2. Your own context dictates which tool to use. 3. Don’t listen to the INDEX/MATCH insurgents. BOOO!
https://ozdusoleil.com/wp-content/uploads/2018/08/vlookup-vs-index-match-the-hottest-debate-in-excel-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-08-27 22:01:592020-11-03 09:42:35VLOOKUP vs INDEX/MATCH: The Hottest Debate in Excel
Of more than 200 pizza orders and 20 ingredients, which trio of toppings shows up the most?
This was more of a mind exercise because the Excel piece was easier than I thought. One challenge is that we want which 3 show up together the most–even if they are with other items. Example:
3 pizzas: – Extra cheese, mushrooms, grilled onions – Ham, extra cheese, black olives, grilled onions, mushrooms – Mushrooms, pepperoni, grilled onions, extra cheese
These all count toward the trio of: extra cheese, mushrooms, grilled onions. We need Excel to see that.
The first step is to get a list of all the possible combinations of 3 toppings:
Out of 20 items, there are 1140 combinations of 3.
https://ozdusoleil.com/wp-content/uploads/2018/08/excel-countifs-wildcards-and-the-most-popular-trio-of-pizza-toppings-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-08-23 23:38:322020-11-03 09:42:37Excel, COUNTIFS, Wildcards and the Most Popular Trio of Pizza Toppings
Recently, Ken Puls of ExcelGuru.ca issued a challenge: Isolate the members who have more than one type of membership and show all of their transactions.
This was tricky for a few reasons: – One person had 2 transactions but for the same type of membership. We DON’T want that person in the final list. – One person had 4 transactions for 3 types of memberships. We want to show all 4 transactions
This video shows my solution. You’ll see GroupBy, a Left Outer Join, and evvythang!
https://ozdusoleil.com/wp-content/uploads/2018/08/ken-puls-excelguru-challenge-isolate-members-and-memberships-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2018-08-10 01:11:512020-11-03 09:42:39Ken Puls ExcelGuru Challenge: Isolate Members and Memberships
