Valuable video. Would highly recommend you to make a separate playlist and videos for data analyst interview(questions, projects, analysis, different business domains-finance, HR, Marketing etc, formulae). I am doing a data analysis course and i have began to see the importance of such interview questions and the way we as a data analyst need to probe the requirement and come up with valuable insights using domain specific formulae. Again, It is a humble plea, do make a dedicated playlist for data analysis interview.

I Enjoy these videos showing real world examples utilising excel functions not normally used.

I love this problem @Chandoo! And I enjoy doing the exercise in Power Query M, because I consider is a powerful instrument! Thank you!

Genius chandoo!.... thanks for such practical Examples, its really helping to broaden our thinking horizon

Brilliant!! Thank you for sharing!

You are doing a great job! teaching Excel to "Excel" :) Always learn something from your videos. Which screen recording tool do you use?

Excellent Chandoo!

Hi Chandoo I gave interview the excel questions were similar to the questions you taught in this video. We need such more videos

In one word "Awesome"... Thanks you....for this 😊

It's amazing 👏

Instead of minifs we can use even filter function.

Excellent Sir

We can use filter and than Countif to count the names if appear more than one

Brilliant👍🌹

very insightful

I would use different methods but learnt a lot from this video.

Awesome

New sub here, greetings from Scotland. Thanks for your helpful videos

can you please show us the shortcuts you used that facilitated the formula typing process? (EG: how to select the whole range without scrolling to the bottom row or transforming instantaneously the numbers into correct date?

It's like a aptitude test ❤❤❤❤

About the use of MINIFS: it doesn’t consider a customer placing multiple orders on one date. By the way, great video topic.

Excellent problems @Chandoo! I would have used Sql in solving such problems rather than excel. Quick question: I the second problem, shouldn't those customers also be counted who had ordered 2 weeks before April as well as there could be a customer who ordered on let's say 28th of March and on 6th of April. I guess this customer should also be counted. Your views?

Dang 👍👍

Excellent work you done But I have query to solve Can you solve this So there are 4 categories. The categories must come to their number per month divided by the days listed below. But it must not exceed the maximum per day that I indicate in the list below. For example, if Tag1: has 786 people, the number must be the same and not higher. Category 1 total monthly: 12431 Category 2 total monthly: 2726 Category 3 total monthly: 1362 Category 4 total monthly: 510 maximum person per day: ( the numbers must correspond exactly to the days as specified, not over and not less!) day 1 786 day 2 744 day 3 719 day 4 701 day 5 692 day 6 684 day 7 689 day 8 680 day 9 677 day 10 683 day 11 679 day 12 671 day 13 659 day 14 639 day 15 602 day 16 584 day 17 570 day 18 554 day 19 533 day 20 505 day 21 489 day 22 462 day 23 436 day 24 418 day 25 391 day 26 369 day 27 344 day 28 302 day 29 279 day 30 255 day 31 233

Hi, I had one doubt in Power BI. I have made one measure (consider Measure 1) on distinct count for respective column which is fine. Similarly I need to pick another column based on this measure 1 which has both values and numerics. How could be done, Sir. UNIQ ID RANGES 077GGG 1 077GGG 1 077GGG 1 078GGG null 078GGG null 079GGG 5 079GGG 5 079GGG 5 079GGG 5 079GGG 5 080GGG 1 080GGG 1 081GGG null 081GGG null 081GGG null In UNIQ ID column. I have used measure as distinct count and in card i will be getting 5 count On the RANGES column, when I use card, I need to get 3 count -----> stating like avoiding the nulls and getting 1 for 077GGG 5 for 079GGG 1 for 080GGG Could u pls help on this, Sir?

Oh man, there are a few problems here. First, the way you determined next order date is not good. Since you used "bigger than" "Order Date" in MINIFS function, it completely messes up next order date for customers who placed multiple orders on the same date. This ends up givin you a wrong answer to the second question. The customer SmartMart ends up being left out, even though they have placed an order in April and they also placed a repeat order within 14 days. It just happens to be within 0 days, since they placed their next order on the exact same date. But the way you set it up, this gets overlooked. Second, I don't think the way you did first question is a good way to determine loyal customers. Like you said, the way you did it, it counts and displays any customer that had at least one repeat order within 7 days of the previous order. But that would include a customer that placed one order, on for example, 2nd of February and placed another order on 8th of February and then never ordered from us again. I would hardly regard someone like that as a loyal customer. What would make more sense to me to filter out loyal customers, would be to find which customers, on average, ordered at least every 7 days. Still, great video and a fun chanllenge, which I always enjoy.

I failed this so hard. I feel pathetic. What I'd like to know is, how long did you need to solve this when you got this problem handed to you? I probably could figure it out, but I didn't give myself much time.

Hi. Fairly straight forward to solve this with DAX measures. Assuming there is a Calendar Table with calculated column formulas for Year and Month name, these measures will work Customers who purchased within a week = SUMX(VALUES(Data[Customer]),1*(CALCULATE(COUNTROWS(Data),FILTER(VALUES('Calendar'[Date]),[Customer count within next 7 days]>0))>0)) Customers who purchased within 2 weeks = SUMX(VALUES(Data[Customer]),1*(CALCULATE(COUNTROWS(Data),FILTER(VALUES('Calendar'[Date]),[Customer count within next 14 days]>0))>0)) Customer count within next 7 days = CALCULATE(COUNTROWS(Data),DATESBETWEEN('Calendar'[Date],min('Calendar'[Date])+1,min('Calendar'[Date])+7)) Customer count within next 14 days = CALCULATE(COUNTROWS(Data),DATESBETWEEN('Calendar'[Date],min('Calendar'[Date])+1,min('Calendar'[Date])+14)) The last 2 measures are input measures for the first 2.

Super great challenge, great solutions, great video, love this kind of stuff that can be controversial!! 😉✌ For fun, a tiny function RO(c,d,f) Repeat Orders ; c: customer vector ; d: dates vector ; f: days "frequency" No helper clms, no ..IFS functions, no 1900 date 😉, single cell formulas: =LAMBDA(c, d, f, LET( r, ROWS(c), s, SCAN(0, SEQUENCE(r), LAMBDA(v, i, LET(q, SEQUENCE(r - i + 1, , i + 1), (XLOOKUP(INDEX(c, i), INDEX(c, q), INDEX(d, q)) - INDEX(d, i))