Lookup Multiplying For Commission Calculation: FILTER, XLOOKUP, LAMBDA & BYROW. EMT 1838.
Вставка
- Опубліковано 13 лип 2024
- Download Excel File: excelisfun.net/files/EMT1838....
Learn about how to calculate commission for each sales rep who sell multiple products that each have a different commission rate. This is a complicated Lookup Multiplying calculation that is made easy with Microsoft 365 Excel.
Topics:
1. (00:00) Introduction
2. (00:26) Commission Rate Data Table Setup
3. (00:53) Old School Formulas and manual Method
4. (01:09) FILTER, XLOOKUP and SUM Method
5. (03:04) LAMBDA and BYROW Method
6. (04:42) Summary
7. (05:04) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula #commission
Mike, if we ever cross paths, the beer is on me. Thank you for everything you do. You deliver without the need of clickbait thumbnails; you deliver concisely and understandably. Cheers!
I've said it multiple times in multiple ways, what better time to say it again than on a multiplying video, Mike you are the best teacher online. Thank you again.
The interaction between formulas is amazing. Another great video Mike, thank again
The way you used BYROW with FILTER is like magic. Thus works when we are able to bring output in single cell. First build simple formula then use BYROW and replace row value with variable. I love this trick.
So nice to see the difference in length of ols school and new school formulas. !!
Super amazing video. I liked the old school where MMult is used. Thank u Mike :) 😊
Great Excel king
Beutilful explanation Professor!!!
Thanks Mike for this EXCELlent video.
Excellent Mike!
Best regards from New Delhi 🇮🇳🇮🇳
Great video about some great stuff!! :-)
Indeed, from an Excel perspective these are great times: with functions like BYROW/COL and MAP we can vectorise the unvectorisable, e.g. vectorise SUM, even though it’s an aggregator.
Thanks, Mike! :-)
Great, thanks Mike.
Thanks Mike :)
Great trick ❤
Token of Gratitude!
Best Wishes to Your Mom!
Thank you, thank you, thank you, Teammate Ankur Sharma!!!!! Your donation really helps me and my Mom : )
Thanks!
Thank you for the donation!!! It helps to keep making videos : )
Superb ❤️
Nice!
FILTER on Commissions could be used as well...
Great 👍
😮😮
Dear,I have a problem, If I have 3 or more columns and I wand to create calculated column with average or median or max,any statistics formula for the values of those column for each row in DAX,are there some ways for that??
Mike, Is there Dynamic formula to return array { the first col is the unique list , the second col is the Total Commission }
For this example, you can use the UNIQUE function to create first column with a unique list. How is your data set up? Like this video? or a different way?
Hi,
Not sure if anyone has ever posted this question before. I'm looking at buying a desktop computer rather than a laptop to run power query and power pivot. What's the best configuration regarding processor,motherboard,memory,etc that I should be looking for? Thanks in advance
Not sure, I am not so much a hardware guy.
@@excelisfun hi,
Thanks for your reply.
What computer do you use? Anything in particular?
Cheers
Excellent video Mike, and thanks for refreshing a blast from the past! I tried to get a little cute here and attempt a single formula solution using LET/LAMBDA, however the result is incorrect. The problem seems to exist in running a lookup and lambda in the same column and the sort doesn't seem to matter. I've run into this problem in the past.
Would you, Geert and the rest of the team please help me identify the problem here? Thanks!!
=LET(
n,A2:A15,st,B2:E15,un,UNIQUE(n),ct,H2:K5,
sc,BYROW(un,LAMBDA(r,SUM(FILTER(st,n=r)*XLOOKUP(r,un,ct)))),
HSTACK(un,sc))
Your "un" is in a different order than G2:G5 so XLOOKUP will deliver wrong rows.
If you replace un,UNIQUE(n) with un,G2:G5 will work fine
On the other hand we expect this to work:
=LET( n,A2:A15,st,B2:E15,un,UNIQUE(n),ct,H2:K5, sc,BYROW(un,LAMBDA(r,SUM(FILTER(st,n=r)*XLOOKUP(r,G2:G5,ct)))), HSTACK(un,sc))
( modified only XLOOKUP(r,un,ct) to XLOOKUP(r,G2:G5,ct) )
This is correct it terms of XLOOKUP but does not work for a reason I have mentioned 1000 times before and everybody is ignoring it:
When we have a single column NEVER use BYROW and always MAP .
Therefore with MAP works fine:
=LET( n,A2:A15,st,B2:E15,un,UNIQUE(n),ct,H2:K5, sc,MAP(un,LAMBDA(r,SUM(FILTER(st,n=r)*XLOOKUP(r,G2:G5,ct)))), HSTACK(un,sc))
BYROW with a single column, if there are preliminary calculations that spill byrow consider them only the first column. They can not spill in memory even if the calculation return a single result.
MAP has no problem spilling preliminary calculations in memory.
Thank you! Appreciate the thorough response. Yes, this is what I suspected and I'm probably one of those who ignored your suggestion. At the end of the day however, we still need to hard code G2:G5 into the equation. No way to get around that other than defining 2 LAMBDA formulas which at that point becomes inefficient and unnecessary.
What would you recommend to do after watching this video series (Microsoft 365 Excel Complete Class: free from excelisfun at UA-cam - 365 MECS ) . Do I need to apply for Job / is there anything advanced I still need to study ? Is there any other video series i need to watch .I am compete beginner in excel and has recently completed the above mentioned video series .Can anyone please guide me ? Thanks