Excel Expert Challenge with Wyn Hopkins of

[@DavidRussell323]

I love these videos. I don't know that much about Excel's specialized functions, but this one was a lot of fun to think about how I'd try to approach this kind of a problem systematically. Initially, I simply thought of the weighted priority solution (assuming that the participants followed the rules), but when AccessAnalytic did some basic data analysis... I lost my mind when finding out that the fictional "Glinda" failed to follow half of the instructions: did a lot of participants submit bad data?? It really threw a wrench in the thought process for me. I wondered what I'd do with all the invalid color answers (and tbh, I'd probably elect to just throw them out for not following the rules). But maybe there's a case to be made for assuming an invalid response as the closest color-match if many of the participants submitted invalid responses, like counting "cactus yellow" as a vote for "mustard yellow". The nice thing about this dataset is that the invalid colors will certainly have the lowest votes, so this kind of attention doesn't seem necessary^ Data scrubbers must have so much patience, thinking about all the extra work just *cleaning* this data was going to require. Shoutout to power query for making it seem easy!! The "what if there are ties in the top 4?" question was a really great point too. Really interesting to watch AccessAnalytic's method for approaching this one--super fun video

[@OzduSoleilDATA]

You comments give me a chill! You're deep into the nuances of the challenge. I gave Wyn the option to do whatever he wanted with the bad entries. One legitimate option would have been just as you suggested: just throw them out and move on with the rest. This is the kind of stuff that makes it hard to work with data. If everything is smooth and people follow the rules, the Excel stuff is easy. But that's not reality. Did some people consider cactus yellow the same as mustard yellow, or was it another write-in like rose-gold? 🤷🏽 Interpretations and decisions have to be made and we accept that we aren't going to get perfection. One of the key moments in my data career was when someone told me: "Oz, just make a decision and I'll back you up. You don't have to call me with every little thing." I love how Wyn handled the oddities. I would have been just as pleased with tossing out the oddities.

[@ExcelwithAG]

Being in Excel field, a rookie maybe, but when I see the experts like you and Wyn spending time thinking and processing like us, oh man it gives a lot of encouragement, in other edited videos we see, they do the stuff with their eyes closed and sometimes I think I can't match the expertise and intelligence they have but no, they think and work same like us, just keep striving. GREAT SHOW.

[@AccessAnalytic]

Glad to share

[@Jolmoodtube]

These challenges are my favorite

[@SAS-my3xu]

This was great! I work in Excel all day and it is great seeing how others approach problems.

[@torkunc]

you can't imagine how these "challenge" video series help us to understand how to approach a problem... thnx a lot

[@OzduSoleilDATA]

THANK YOU! I wasn’t sure how these would be received. I’m so happy that everyone has been so engaged and find these valuable. 🤗

[@jenniferdustin8214]

I'm really proud of myself, I copies the data, and solved it before watching, and I did everything you did! (Except for trimming and uppercasing) This is great practice for those of us that think we know what we are doing!

[@GrainneDuggan_Excel]

Oz and Wyn, that was fantastic. You got me so involved, I was shouting at the screen with my ideas or corrections . 🎉🎉🎉🎉🎉❤

[@AccessAnalytic]

Hah! I was doing the same at myself afterwards 😂

[@GrainneDuggan_Excel]

@@AccessAnalytic Wyn you were wonderful. I loved that you were hunting for a few buttons and had to think about which join you needed. That is exactly what real life is like, especially when we have an audience! You still caught all the possible data quality issues. Marvelous!!!

[@AccessAnalytic]

@@GrainneDuggan_Excel 💚

[@AccessAnalytic]

Thanks for inviting me Oz!

[@OzduSoleilDATA]

YES YES YES! You put out a lot of gold in this one. Thanks again. 🙏🏽

[@txreal2]

Wyn’s thinking process is priceless!

[@OzduSoleilDATA]

INDEED!

[@ousmanesidibe951]

By the way, i constantly watch contents from these two great MVPs, So i wanna thank them for sharing all the hard way found knowledge.

[@wesszep9865]

May this year be an amazing one and joyfull. Happy new year mr Oz, wish you all the best and a lot of good content, God bless you

[@aman_mashetty5185]

love to see both as always as both rocks...!

[@AccessAnalytic]

Cheers!

[@wcthrill]

These challenge vids are really informative

[@therobinjohn]

Wowwww he’s GOOD!!! This is a master class seriously

[@AccessAnalytic]

Cheers ☺

[@rolexcel]

Working in a very challenging data environment we regularly encounter new and unexpected problems to solve with time constraints and a lot of money at stake. Whilst it can be frustrating I always remind by boss and colleagues that most of us would not have our jobs if everything was always perfect. The bad side of this is that, while we do we solve these problems, higher management do not appreciate how intense and challenging it can be.

[@angelaisland4036]

Very cool format!

[@stevereed5776]

Would be great to see the solution using formulas

[@OzduSoleilDATA]

Here is is: kzbin.info/www/bejne/fHrGqayCpt-Kmac

[@SimplyDigitalLK]

Very good challenge. And cool video to check out. Im really wondering what would be the formulas aproach to solve that. Power query is most likely the more effective in this situation, but well - the formula solutions could be also so much fun to watch.

[@OzduSoleilDATA]

Interesting that you propose a formula solution. Wyn has already recorded a formula solution that’s he’ll post next week. When he’s done that, I’ll amend the description to include the link. 👍🏽

[@SimplyDigitalLK]

@@OzduSoleilDATA Oh wow.. perfect. Lookinig forwarad to see that :)

[@nemoyatpeace]

Do we have access to the challenge file to try on our own?

[@OzduSoleilDATA]

Yes. There’s a link in a card in the video.

[@nemoyatpeace]

@@OzduSoleilDATA Thanks! Found it, fun challenge.

[@McIlravyInc]

Maybe you should have one of those that asks you how to get better as a guest and then mentor them through the solution. They may find they are better than they think and the video could encourage and teach others.

[@OzduSoleilDATA]

Really really good idea 💡

[@txreal2]

Sure let me just work out the problem first 😂

[@samualweissen6272]

Could not agree more on the need for more live content or the need to show trial and error. So much Excel and general programming videos never show the actual process and the mistakes we all make along the way, just a clean solution. It is important for all of us to know that it is a process and we all make mistakes and not all formulas work the first time typed. From bad logic to simple typos it happens to everyone. Debugging is process and one that is almost never addressed in Excel videos only more traditional programming language tutorials.

[@AccessAnalytic]

Great point

[@nevenmesic2856]

Better for reverse engineering it and checking would be to leave the data with people and votes 1 2 3 assign the points leave everything and lets say approx have 270rows after unpivoting data... Then load data as a pivot and then just click on a cell to drilldown and sense check top 4 or so...

[@ousmanesidibe951]

Someone correct me if my values are wrong: Value Count Orange 24 Purple 24 Indigo 23 Neon Pink 21

[@mrbartuss1]

Midnight Blue is laso 21

[@OzduSoleilDATA]

Interesting. I’d need to see your process. 🤔

[@mrbartuss1]

@@OzduSoleilDATA This is the result without the rank. Just counting how many people voted for a given color

[@OzduSoleilDATA]

@@mrbartuss1 GOTCHA! Ok. 👍🏽

[@viktorasgolubevas]

if use the "olympic" ranking :) Neon Pink Midnight Blue Purple Indigo =TAKE(SORTBY(Available, COUNTIF(Votes[Vote1],Available),-1, COUNTIF(Votes[Vote2],Available),-1, COUNTIF(Votes[Vote3],Available),-1), 4) edit: removing duplicates a bit changed "olympic" results :)) Neon Pink Purple Indigo Midnight Blue

[@McIlravyInc]

@@viktorasgolubevas i was thinking about the different ways to rank as well! That really highlights how important gathering good requirements is. Also why I ask for sample data ad results when improving then automating so I can truth my requirements and assumptions I used to fill in the blanks!

[@OzduSoleilDATA]

Interesting 🤔