Excel Challenge & Meditation | Isolate the winners of split-decisions | Dynamic Arrays

[@wynhopkins4023]

I feel relaxed and educated 😆

[@OzduSoleilDATA]

Yessssss … the perfect state for Excel-ing. 🧘‍♂️

[@spilledgraphics]

1. Woah... 🤯 that "F**ing text message" is very unpolite for an Excel Sensei or Crap Data Crusader like you Oz. 2. The Hoodie is fantastic 😁👌👌👌👌 3. Wow... did NOT know about the other optional arguments in the UNIQUE function! Thank you Oz! 🙏

[@OzduSoleilDATA]

Which optional arguments did you not know about?

[@PhillStone]

Thank you for opening my third eye.

[@OzduSoleilDATA]

😊 you’re so welcome 😁

[@stonebarnard9105]

Thanks for the Edutainment Oz! I drank a Redbull before watching this video and blasted a diabolical =IF(C3=D3,C3,IF(C3=E3,C3,IF(D3=C3,D3,IF(D3=E3,D3,IF(E3=C3,E3,IF(E3=D3,E3,"Check again")))))). Now that I have calmed down, I have meditated and converted to using the UNIQUE and FILTER combo. Much appreciated!

[@OzduSoleilDATA]

Meditation helps 😄 Thanks for sharing your initial solution. It’s nice to see other approaches. 💪🏽

[@wayneedmondson1065]

Nice one Oz! Thinking (meditating) outside the box with UNIQUE and FILTER. Thanks for the demo. Thumbs up!!

[@OzduSoleilDATA]

WAYNE! Thank you!

[@danielgoncalveslima9350]

Hi, I learn to use filter that resolve well the problem, =unique( filter(matriz; Countif(matriz; matriz)=2); true) It's Works ... Sorry my english... I Am brazilian 😅

[@barttitulaerexcelbart9400]

Nice Problem Oz, "apear only once" ...you don't need that so often, but you gave a nice example. Since I am still a bit "old school"... I was thinking of 3 help columns with COUNTIF (result 1 or 2). And then with an INDEX MATCH find the corresponding name. Just find the first "2" since the other "2" has the same name...

[@OzduSoleilDATA]

Interesting. I thought about going a COUNTIF direction. It’s an acceptable way to get the answer.

[@hammerdata1603]

Nice work Oz! Thanks for entertaining us while we learn, FILTER is definitely a function I need to start incorporating into my work regularly.

[@OzduSoleilDATA]

FILTER is a strong function. Very handy.

[@Wallawallawallawalla]

“Who the hell is Oz? And where’s my spreadsheet?” 😂

[@OzduSoleilDATA]

😊

[@spykecarter]

This was brilliant and funny, thanks Oz, keep up the good work.

[@OzduSoleilDATA]

😁 I had fun creating it. I’m glad you enjoyed the video. 🙏🏼

[@splendidfd6848]

With only two contestants and three judges one if can do the job: if(C3=D3,C3,E3) If the first two judges voted the same their choice is the winner, if they differ then the third judge's choice will breaks the tie. Of course if you try to extend the problem (more judges, more teams, or the possibility of a tie) then if is instantly outclassed by the new array functions.

[@OzduSoleilDATA]

Ah! Very concise. 🔥🔥🔥 Much simpler than mine. 💪🏽

[@XLarium]

Great simple solution.

[@timmybone13]

This is great. Are these functions new? I don't always know when watching videos if my work's version of Excel has the same capabilities.

[@OzduSoleilDATA]

Yes. That’s frustrating for all of us. 😣

[@timmybone13]

Yeah, no problem. Just good to know which is which. I assume the 365 version would have these?

[@hammerdata1603]

@@timmybone13 Yep, 365 has both of these functions.

[@Faisal63045]

Hi I’ve PQ challenge The daily Work Hours: Start from 8:00:00 AM End time 10:00:00 PM The challenge 1- is to change any transaction hours that comes after work time to the start of work time on the Next day (8 AM) 2- is to change any transaction time between (12:00:00 AM to 7:59:59 AM) AM to 8:00:00 AM

[@rubenmunozverdu7528]

What if there is no loser because the three judges voted the same? Or worse, what if each judge voted different (all losers/no winner)?

[@OzduSoleilDATA]

What I had in mind was BattleBots. The only outcomes: knockout, unanimous decision, split decision. If we wanted to include unanimous decisions in the dataset, the data would need to be set up different. That would likely change the meditation entirely.

[@rubenmunozverdu7528]

Ommmmmm

[@OzduSoleilDATA]

@@rubenmunozverdu7528 actually, if all we do want is the list of winners, and not the losers, an IF statement could be used to identify unanimous decisions vs. split decisions.

[@riyazkhan-xm2qn]

I need a formula about (20 the value count =1 ) If I entered (40 the value count =2 and 100 the value count = 5 and 1000 the valu count change = 100 could you help me with what formula it was? Could you help me with this?