LARGE Function, Averaging Top Scores and Over-Thinking

/in /by

This one is about over-thinking. A friend asked how to average the top 3 of 4 scores. I started thinking about Dynamic Arrays, unpivot and maybe the opportunity to show off some Power Query. But when I went with my first and dullest idea, that’s what gave her exactly what she needed.

I used the LARGE function to pick:
LARGE, 1 (largest value)
LARGE, 2 (second largest value)
LARGE, 3 (third largest value)
add them up and divide by 3. DONE! BOOM!

Sometimes the simplest, dullest solution is all that’s necessary.

#LARGEfunction

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Dependent Dropdown Lists in Excel with Power Query & Dynamic Arrays

/in /by

When we build models in Excel we must anticipate crazy entries. In this tutorial we try to prevent crazy matches between labels and containers. A 4×5 label won’t fit on our 3-ounce spray bottle, and a 1×1 label would look crazy on a 1-gallon jug.

First, we use Power Query and Unpivot to organize our source data. Then, we use the new functions UNIQUE and FILTER to make the dependent drop down lists, taking advantage of Excel’s new calculation engine.

Those work fine, but there’s a known problem with dependent dropdown lists (aka cascading dropdown lists): it’s easy to end up with crazy matches because the lists don’t re-set. It’s easy to select something with the child list and then change the parent list to something crazy. I handle this by using COUNTIFS and the WingDings font to flag crazy entries; i.e. create an alert.

Here is a link to Leila Gharani’s video where she solves this problem by using dependent combo boxes: https://youtu.be/aSPtWo3IiOM

#DynamicArrays
#DropdownLists
#DataValidation

======

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Using RANDARRAY for a Random Data Generator in Excel: Random Names

/in /by

For several years I’m maintained my own random data generator in Excel. It makes heavy use of RAND and RANDBETWEEN. However, with the use of Dynamic Arrays and the new RANDARRAY function I’ve been rebuilding the random data generator.

RANDARRAY allows so much more flexibility and 4 formulas get me as many names as I request.

This video shows how RANDARRAY, COUNTA, Tables and the new calculation engine in Excel can easily generate 900 first names, last names and middle initials.

#RANDARRAY
#DynamicArrays
#RandomDataGenerator

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Identify Overlapping Times with Excel’s SUMPRODUCT & Conditional Formatting

/in /by

When people have to list their start times, end times and their tasks, how can we tell if any of the times overlap?

One time I solved this by writing a 25-level nested IF statement to compare each of 25 rows of times against each other. It worked. It was ugly, but it worked.

In this video I show a much simpler solution using SUMPRODUCT to flag rows that have overlapping times. In the background, SUMPRODUCT is an array function that does the comparisons instead of a bunch of IF statements in a messy formula that’s hard to build and troubleshoot.

I take it an extra step and apply conditional formatting to automatically highlight rows that have time overlaps.

#SUMPRODUCT #TimeOverlaps #ConditionalFormatting

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Dynamic Arrays: Identify Rows That Have Duplicates Using COUNTIFS and Excel’s New Calculation Engine

/in /by

This video is based on a solution that Alan at Computergaga solved by using Data Validation (see his video: https://youtu.be/1GDgTN4lvAo)

Of course, I started thinking: Dynamic Arrays. The challenge:
How can I flag a row that has duplicate entries?

My solution doesn’t use any of the new functions (e.g., FILTER, RANDARRAY, SEQUENCE, etc.). Instead, it takes advantage of the new calculation engine and uses COUNTIFS and MAX. The solution was much easier than I expected.

After we solve the problem in this video, we can have a little fun! How about using the Wingdings font to create the flag for offensive rows?

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel’s Flash Fill vs Power Query’s Column by Example

/in /by

This video shows 2 examples of messy data and how they’re handled by Excel’s Flash Fill and Power Query’s Column by Example.

We have to merge first names and last names but also, deal with all caps, lower case, and get the names into proper case, but also there are prefixes like ‘Rev’ that we don’t want.

Flash Fill proves to be more robust–able to handle more complex situations, but the results are static. Column by Example, however, can be automated and that’s it’s most glorious aspect.

Watch the video and see how they compare.

#FlashFill #PowerQuery #ColumnByExample

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

The New & Improved RANDARRAY Dynamic Array Function

/in /by

Lordy! This is a good one! Just within the past week the RANDARRAY function was improved! Dynamic Arrays, the functions and the new calculation engine just keep getting better.

RANDARRAY. WOW! This is better than both the old RAND and RANDBETWEEN functions!

This video shows 2 examples of using RANDARRAY. In the olden days, we would have needed more than just this one function. You will also see RANDARRAY used with INDEX and COUNTA! Everything you possibly could need … it’s all here, brothers and sisters. It’s all here, and it’s FIRE!

#DynamicArrays
#RANDARRAY
#ExcelFunctions
For more Excel and an intro to Get & Transform (Power Query) see my Lynda/LinkedIn courses:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

6-Question Quiz on Dynamic Arrays

/in /by

You’ve seen the Dynamic Array videos. Now, it’s time for a quiz.
6 questions to test your fitness for battle in a world where:
Cleansing data is brutal
The consequences are real, and
Crap Data is heartless

To help: Here is a playlist of Dynamic Array videos from other YouTubers:

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Dynamic Array: Unstack Data with SEQUENCE … just 2 steps

/in /by

Here we are again!
I was playing around more with the Dynamic Array functions, especially after Leila Gharani posted her video that made unstacking data pretty easy with a substitution trick and finding a pattern in the dataset. Here is her video:

Well, I found a 2-step process that REALLY simplifies unstacking a column of data. SEQUENCE, COUNTA and INDEX get us there, taking advantage of spilled arrays and Excel’s new calculation engine. Alas, not everyone has the Dynamic Arrays yet. If you don’t have them, and you need to unstack some data, use Leila’s method. 👍

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2