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

Respect for pronouncing etc correctly

Love this.

Thank you sooooooooooooo much Smooooth. Really its a great jobb

Thanks man! It was really fun learning.

Dear Oz, The new functions have made it easier to solve the challenge, including the new ":." range syntax: =IFERROR(BYROW(F3:.F42,LAMBDA(a,IF(ISEVEN(CONCAT(REGEXEXTRACT(a,"\d+",1))),"EVEN","ODD"))),"ODD") 🤗

No fluff, just straight to the point. Awesome!

आपकी वीडियो एक से बढ़कर एक है

बहुत अच्छी है

युटुब पर मैंने बहुत वीडियो देखी लेकिन आप से अच्छी वीडियो पूरे युटुब पर नहीं है❤❤❤❤

Nice!!

Love you brothers.

I love you brother! hate that I just found your videos today, but I'm about to bing watch. You break everything done in a real world context! I finally understand how to import from a folder after watching 30 other videos lol

These challenges are my favorite

Good. But what if you need to do it inside a table?

👍

Just love these challenges. I learn so much. I have a tiny challenge for you. My excel file comes with 12 columns and many rows, however, data is in groups of 6 rows. The first 3 rows of the group are headings the following 3 rows of the group are values that correspond with each of the 3 headings. Need to have only one row of headings (36 columns).

Only thing I really did different was I added a column next to the rooms, and did a formula to see if the room worked for the sqft needed. I did > than the needed the sqft, and < 1.5 the sqft, to catch if its too big as well.

I did the same text split for the morning, afternoon, evening, and then I did this formula in the next column. =LET(who,E2,day,B2,previousDay,day-1,Site,C2,previousSite,XLOOKUP(who&previousDay,$G$2:$G$49&$B$2:$B$49,$C$2:$C$49),output,IF(NOT(ISERROR(previousSite)),IF(C2=previousSite,65,90),"No"),output) Then I did a pivot table.

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...

That was AWESOME!

My PQ response: let Source = Excel.CurrentWorkbook(){[Name="tShifts"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Shift1", type text}, {"Shift2", type text}, {"Shift3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Day"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}, {"Value.4", type text}}), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Day", "Attribute"}, "Attribute.1", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute.1"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each 8), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Hours"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}), #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Value"}, {{"Total Hours", each List.Sum([Hours]), type number}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Overtime", each if([Total Hours] > 40) then [Total Hours]- 40 else 0), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Regular Time", each if([Total Hours]> 40) then 40 else [Total Hours]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Regular Pay", each [Regular Time]*46), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Overtime Pay", each [Overtime] *(1.5 * 46)), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"Regular Pay", Currency.Type}, {"Overtime Pay", Currency.Type}}) in #"Changed Type2"

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!

I turned it into a table called tOrders. First I made a N6 the starting point for the pick 6 using: =6*COUNTIF(tOrders[Pick6],"x") Then I added a pick 6 column in M. The first cell (Caramel Popcorn) got this formula (=RANDBETWEEN(0,6)) and the rest got this formula (=RANDBETWEEN(0,$N$2-SUM($M$3:M3)) dragging it down so the last M3 went up with each cell. That gave me a random number in each row, but they still all added up to N6. Next, I added the initials to column P behind their respective flavors. Then, in column N, I went with this formula (=SUM(tOrders[All])+SUM(INDIRECT("tOrders["&P3&"]"))+M3) where M3 is the pick 6 column from above for each flavor, and P3 is that flavors initials. so basically, you can the sum of the all column since each number there represents 1 of every flavor, it gets counted for every flavor, plus the sum of the flavors individual column, plus the pick 6 field for that flavor.

Thank you! This was short and sweet and easy to understand!!

🎉🎉 Thank you so much! I’m a big fan

Yo Oz, where did you get that jersey? I want to order one as well

The camera tool is epicc! Thanks Oz!

Damn! Since i am not power query or automation user this really popped up my mind - very interesting thanks

Q, when I clear the 'x' on the 'Pick 6' it still shows as TRUE and doesnt update the calc.. I then need to clear that cell. When Celia did it, Excel would automatically update. Im using Excel 365 for web.. Also can use SUM with XLookup to add Total of each Cupcake. Simpler formula

FYI<,the file is no longer available

These challenge vids are really informative

Dude u r my hero ❤

Would be cool if the data c/b downloaded for anyone to work on

❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤

That was an interesting one. I was just messing around with Expand function today and I was happy to run into your example.

Thank you so much for this. This was exactly what I needed!

You da man!!!!!!!

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

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.

Nice! Thank you.

This felt way too good. I may have to report this to mark zuckerface. 😆

Options are there definately but this trick is cool and short!!😊... Thanks OZ!!

Table.AddRankColumn is the M function Wyn was looking for. It's not part of the UI and you need 365 or Power BI to have access to the function. Table.AddRankColumn( table as table, newColumnName as text, comparisonCriteria as any, optional options as nullable record, ) as table

Wyn’s thinking process is priceless!

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.

Would be great to see the solution using formulas

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

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

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.