Advanced Array Formula: FILTER or SUMPRODUCT to Simulate a Relationship Between Two Tables! EMT 1748
HTML-код
- Опубликовано: 9 авг 2021
- Download Excel File: excelisfun.net/files/EMT1748....
Learn how to create a single cell advanced array formula to create a sales report from two tables.
Topics:
1. Intoduction
2. SUMPRODUCT & VLOOKUP formula
3. FILTER & XLOOKUP formula
4. Sumamry and closing
Here are some testing results are various formulas on 200,000 rows:
Timing results from 200,000 rows:
1) =ROUNDUP(VLOOKUP(J3:J6,dProduct,2,0)*TRANSPOSE(BYCOL((fTransactions[Product]=TRANSPOSE(G3:G6))*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity],LAMBDA(a,SUM(a)))),2)
165 mil sec
2) =ROUND(XLOOKUP(Q18:Q21,dProduct[Product],dProduct[Retail Price])*BYROW(Q18:Q21,LAMBDA(EachRowInArray,SUMPRODUCT(--(fTransactions[Product]=EachRowInArray),(1-fTransactions[RevenueDiscount]),fTransactions[Quantity]))),2)
205 mil sec
3) =ROUNDUP(VLOOKUP(G3:G6,dProduct,2,0)*BYROW(G3:G6,LAMBDA(a,SUM((fTransactions[Product]=a)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity]))),2)
225 mil sec
4) =MAP(Q18:Q21,LAMBDA(RowValue,LET(FilterTRUEs,fTransactions[Product]=RowValue,SUM(ROUND(XLOOKUP(RowValue,dProduct[Product],dProduct[Retail Price])*(1-FILTER(fTransactions[RevenueDiscount],FilterTRUEs))*FILTER(fTransactions[Quantity],FilterTRUEs),2)))))
250 mil sec
5) =MMULT(TRANSPOSE(--(fTransactions[Product]=TRANSPOSE(J3:J6))),ROUND(VLOOKUP(fTransactions[Product],dProduct[#Data],2,0)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity],2))
260 mil sec
6) =SUM(ROUND(XLOOKUP(G3,dProduct[Product],dProduct[Retail Price])*(1-FILTER(fTransactions[RevenueDiscount],fTransactions[Product]=G3))*FILTER(fTransactions[Quantity],fTransactions[Product]=G3),2))
316 mil sec
7) =SUMPRODUCT(ROUND(XLOOKUP(J3,dProduct[Product],dProduct[Retail Price])*(fTransactions[Product]=J3)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity],2))
345 mil sec
8) =BYROW(G3:G6,LAMBDA(a,ROUNDUP(VLOOKUP(a,dProduct,2,0)*REDUCE(0,(fTransactions[Product]=a)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity],LAMBDA(v,a,v+a)),2)))
490 mil sec
9) =BYROW(J3:J6,LAMBDA(p,SUM(FILTER(ROUND(VLOOKUP(fTransactions[Product],dProduct[#Data],2,0)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity],2),fTransactions[Product]=p))))
760 mil sec
Anyone got other formulas?
Looks like a drag race of hyper cars😂😂 and Mike went for the WIN 🏆🏆
Have you tried the good old mighty helper column ? (added column "help" ( =(1-[@RevenueDiscount])*[@Quantity] )
=ROUNDUP(VLOOKUP(G3:G6,dProduct,2,0)*SUMIFS(fTransactions[help],fTransactions[Product],G3:G6),2)
This is not a hyper car formula, is a old rusted VW Golf tunned under the hood 😂😂✌
Also try this one, Vlookup can live outside any formula,,
=ROUNDUP(VLOOKUP(G3:G6,dProduct,2,0)*BYROW(G3:G6,LAMBDA(a,SUM((fTransactions[Product]=a)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity]))),2)
Or this one:
=ROUNDUP(VLOOKUP(J3:J6,dProduct,2,0)*TRANSPOSE(BYCOL((fTransactions[Product]=TRANSPOSE(G3:G6))*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity],LAMBDA(a,SUM(a)))),2)
-on this one BYCOL only sums in the lambda section, comparative operators are already calculated in the array argument, not included in LAMBDA, like in BYROW
@@Excelambda , yes, helper columns are very useful in worksheet, power query and DAX. I just had a situation with DAX where the helper columns were beating the single cell Measures by a lot!!! Love those rusted VW Golf tunned under the hood : ) : ) : )
@@Excelambda New winner: 225 mil sec... : ) Go Team!
=ROUNDUP(VLOOKUP(G3:G6,dProduct,2,0)*BYROW(G3:G6,LAMBDA(a,SUM((fTransactions[Product]=a)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity]))),2)
Very clever to bring VLOOKUP out front!
Great use of XLOOKUP and FILTER
Glad you like it!
Thanks Mike. Old school was Great for me!!
Glad you like it, John!!!!
I like typing out the old school formulas, because it feels more satisfying.
Yes!!!!! Old School = Satisfaction Guaranteed ; )
You are so talented. You are the best teacher.
Thanks, Hoi Yin Wan : ) : ) : )
Thanks Mike for this EXCELlent video.
You are welcome fellow teacher MM Syed : ) : ) : )
Great! Thanks! This time I like the old school more :)
Yes! Great to see you, friend : ) Old School rules!!!!
Thanks Mike! Awesome, as always. Lots of good comments below too. Thumbs up!! Go Team!!!!
Go Team!!!!!
Thank you so much for showing old and new school Mike.
Amazing as always Mike. Thanks for the tips.
You are welcome for the tips!!!
Thanks Mike... I wish I had watched this as soon as it was published. I found a solution here that frustrated me yesterday for an hour. As always, great content.
What did you find that helped?
Great explanations for both styles, love it! Thank you Mike!
You are welcome!
Boom!Both Formulas Are Equally Awesome...Thank You Mike :)
And, Boom: it is a tie : ) : ) : ) : )
Your channel is perfect sir
Glad you like it, Devni!!!!
Old school is awesome 👍
Old School still Rules!!!!
Thank you Mike for doing the old School method. It is always so much fun to compare between the two. In this one I would prefer the new school
Be sure to look at pinned comment at top for many more cool formulas!!!!!
Both Ways are cool.it is just matter of people which version of Excel they have... Super cool video.
Thanks : )
Awesome. Not too many companies upgrading their excel, so thanks for doing the old school way. Maybe in 10 years I’ll get to use x-lookup and such 😂
Old School Rules!!!! : )
Thanks Mike
You are welcome, M. Alomery!!!!
Filter + Xlookup for me ;) Thank you Mike!
Cool! FILTER & XLOOKUP are fun ; )
thank you so much sir for this video
You are welcome, Adjal!!!
I like them both...
Glad you like them : )
Both are EXCEL-ent Mike, thanks!
You are welcome, Chris M : ) : ) : ) : )
Just need to order my Excel Is Fun & Power Queries Is Fun coffee mugs & I can have my "Big Bang Theory" tea party.
I drink my coffee eacg day out of those mugs. I think the coffee takes more fun that way ; )
Thanks for the support, Jack!
great
Hi Make, thanks for the video, useful as always. I like that you use new Office 365 functions in your videos, I really have to be more acquainted with them.
Microsoft 365 Excel is the best : )
Old school 👍🏻
Old School is fun!!!!!!
Great video!! ✌✌
For fun, with the new ones, single cell
=BYROW(G3:G6,LAMBDA(a,ROUNDUP(VLOOKUP(a,dProduct,2,0)*REDUCE(0,(fTransactions[Product]=a)*(1-fTransactions[RevenueDiscount])*fTransactions[Quantity],LAMBDA(v,a,v+a)),2)))
That's crazy, two series of distinct array calculations. Thanks, Exceλambda!!! I have added it to the download workbook for the Team : )
Here is another one:
=MAP(Q18:Q21,LAMBDA(RowValue,LET(T,fTransactions[Product]=RowValue,SUM(ROUND(XLOOKUP(RowValue,dProduct[Product],dProduct[Retail Price])*(1-FILTER(fTransactions[RevenueDiscount],T))*FILTER(fTransactions[Quantity],T),2)))))
I timed the MAP, LAMBDA and LET and it averaged about 250 mil sec on 200,000, where as the BYROW, LAMBDA and REDUCE one averaged about 590 mil sec over 200,000. Go Team!!!!!
@@excelisfun Super mega cool!!✌✌Only one of the new functions!! Fantastic!!!!👍👍👍😊
@@Excelambda I can't really seem to find many good uses for REDUCE. The SUM function always seems to do it better. What do you think?
@@excelisfun What is biggest drawback of Excel ever? ...IFS functions with their range arguments and not arrays (we discussed about this before and my opinion was that is kept like this on purpose, for safety reasons, the ranges live on sight, on real estate of the spreadsheet, easy to inspect)
I have addressed this drawback only with recursion and MMULT, now this comes to rescue. This is what reduce can do, anycalculationIFS (PRODUCTIF is already on Office help page as an example). This is why I used it here, a SUMIFS with arrays arguments in disguise. 😃. With time, more stuff will be revealed. Our problems are too small for what this new functions can solve. Before them, we were on Newtonian Excel , now we go on Quantum Excel 😂
Thanks Mike, I finally got Office 360 so going back through all the vids for the updated formulas. You mentioned that SUMPRODUCT took less time to type out but still took half the time to create the "new" XLOOKUP formula :) Also how do you time your formulas down the the milliseconds? For my personal files I think I'd prefer the new formulas but for anything I had to share doing it the old school way since so few have the latest version of Office currently.
I time with Charles Williams Fast Excel add-in : )
And, yes, as analysts, we definitely need to know how to do old school so we can share. We will need old school still for years to some : )
✅
Cool! Hope to get office 365 soon!
I hope you do too : )
Mike, I saw that you had to perform the long hand FITLER twice in the New School formula.
What if you filter the (suitable portion of the) entire table just once and store it in a local variable using the LET function?
Enabling you to do the FILTER only once? Could save you some calculation time…
But then you have to pull columns with INDEX, I think... That is conceptually why I did not do it. But if there is one lesson I know form decades of Excel work, you never know until you try... : )
@@excelisfun correct. And that’s why trying to make single all-self-contained lambdas is a bit of an art in itself.
Some of us can go really extremely far in doing all that. But it is fun and enables us to hide most if not all of the complexity involved.
I refer to the mr.Excel Message Board for plenty of examples of deep dive lambdas.
@@GeertDelmulle Yes!!!!!! Go LAMBDA Team : ) : )
Cool
Glad it is cool for you!!!!
Mike will you please make a video about circular dependency error in DAX
Hi Mike! Great video. I'm constantly learning from your videos.
Any suggestions for when your data set is not great?
For instance, if you are looking up a client to sum hours worked, and the data is formatted as "Client - Work Done for Client" in one column, but you want to just search for the client and sum up total hours regardless of work done.
I've used various text functions to get an array of clients, however I cannot get the formula to sum successfully.... I'm sure the FILTER function may be able to help in this situation as well.
Caveat - I don't want to use a helper column if possible. I would rather use an array formula...
I just watched your video "Ctrl + Shift + Enter: Excel Array Formulas 12: SUMIFS, COUNTIFS, SUMIF, COUNTIF, AVERAGEIF" and got it to work with SUMPRODUCT! But I kind of want an Office 365 way to do it... But anyway, I'm happy. :)
@@josephjacobs9488 I am sorry, but I am not understanding what you are doing. I am usually not that smart and slow at understanding...
@@excelisfun I probably did a bad job at explaining it lol.
I was basically just trying to do SUMIFS with an array in the Criteria Range section which obviously was not working.
Using SUMPRODUCT allowed me to get the result with one criteria, but now my issue is breaking it down into multiple criteria.
In short, I'm totaling hours worked by category, and I'm trying to sum those hours by week. I'm working on the second part now.
Thank you for the reply. :)
Cool, thanks.
I try to use new Lambda helper function.
=BYROW(J3:J6,LAMBDA(p,SUM(FILTER(ROUND(VLOOKUP(fTransactions7[Product],dProduct8,2,0)*(1-fTransactions7[RevenueDiscount])*fTransactions7[Quantity],2),fTransactions7[Product]=p))))
Or MMULT
=MMULT(TRANSPOSE(--(fTransactions7[Product]=TRANSPOSE(J3:J6))),ROUND(VLOOKUP(fTransactions7[Product],dProduct8,2,0)*(1-fTransactions7[RevenueDiscount])*fTransactions7[Quantity],2))
Thanks for the cool formulas!!! I will add to download workbook : ) Thanks, Teammate Excel Wizard!!!!
Here is another alternative that I came up with:
=MAP(Q18:Q21,LAMBDA(RowValue,LET(FilterTRUEs,fTransactions[Product]=RowValue,SUM(ROUND(XLOOKUP(RowValue,dProduct[Product],dProduct[Retail Price])*(1-FILTER(fTransactions[RevenueDiscount],FilterTRUEs))*FILTER(fTransactions[Quantity],FilterTRUEs),2)))))
I timed your formulas and some others. I pinned the comment to the top. Go Team!!!!
@@excelisfun Wow, there is a lot more to learn with the Lambda helper function.
@@ExcelWizard That is so true. And... the function REDUCE seems pointless. I can't find much of anything that the SUM function can not do better...
Tha't Amazing Mike ....i liked the "MMULT" version .... but wait ...theres are "Map" and "BYROW" functions ? ... i must have been sleeping too long.
The functions just came out today for me. I will make a video soon : )
Thanks Mike, I always wondered why in VLOOKUP, for the third argument (column index) 1:09 we cannot use the name of the column instead (dProduct[Retail Price])? Also, I assume that with the FILTER function there is no other possibility, could we filter first then do the maths with an IF function, instead of two FILTER functions (one for the discount and one for the product name) ? 😊
You can use MATCH function in third argument of VLOOKUP when you want to use the field name.
Many times with FILTER, you can use the IF function as an old school replacement : )
How did you calculate the times each formula took to calculate? Is there a tool somewhere?
Excel MVP Charles Williams has made the timer that most Excel people in the world use: www.decisionmodels.com/FastExcelV4.htm
I have a question can you please help i have two similar tables of YTD can i create third table of FTM by subtracting both the tables in power query any one can guide please ?
In Excel subtracting tables is rediculously easy. A formula like this would work:
=YTDFirst-YTDSecond
In Power Query I do not know how to directly subtract one table from anothre. I only know how to do it one field at a time. Here is some M Code that can subtract two YTD tables, each with three columns of numbers:
let
GetFirstTable = YTDSecond,
AddedIndex = Table.AddIndexColumn(GetFirstTable, "Index", 0, 1, Int64.Type),
Diff1 = Table.AddColumn(AddedIndex, "Difference1", each [Number 1] - YTDFirst{[Index]}[Number 1],type number),
Diff2 = Table.AddColumn(Diff1, "Difference2", each [Number 2] - YTDFirst{[Index]}[Number 2],type number),
Diff3 = Table.AddColumn(Diff2, "Difference3", each [Number 3] - YTDFirst{[Index]}[Number 3],type number),
RemovedOtherColumns = Table.SelectColumns(Diff3,{"Difference1", "Difference2", "Difference3"})
in
RemovedOtherColumns
Hello, we are unable to download files from E-DAB and few more playlists, can you please let us know the reason and also please provide other way to get the files for practice
Sever is down. Highline College is working on it.
@@excelisfun thank you for the information... Have a nice day ❤️
Hi I need your help in my data I have 5 sheet now I want to make pivot table all sheet have different data relationship not possible thank in advance
For back and froth dialog to get Excel solutions try this awesome Excel question web site: mrexcel.com/board
Dear excel. Can you please teach this equation in power query
You do not need a manual formula. You can just use the Merge (Join) feature. Here is the most complete video I have: ruclips.net/video/-kle5a7vbRA/видео.html
Here is a less comprehesive one: ruclips.net/video/8F7v6YvnsiY/видео.html
You maybe should have rounded after sumproduct not before
I don't think so. Why do you say so?
@@excelisfun well, they do not give you the same answer . If the sig digs are significant and rounding is for pro forma reporting, then rounding is ...for pro forma
@@stephanweaver1960 Yes, they do not. For accountants, each transaction dollar amount must be rounded. that is why I rounded before : ) Analysts sometimes are not as picky and may round at some other point.
1st comment
BIG 1st place trophy for you!!!!!!!
Too complicated. Do we need such an formula to fet these type of result? Why do we need to hack our brain for this?
In fields like finance, statistics, economics there are a lot of multi-step calculation processes that can be condensed down to single cell array formulas. Because none of the intermediate steps are needed and there are so many calculations, it is really helpful to condense many steps into one formula. In addition, some reporting tasks greatly benefit from single cell array formulas. But for most of what we do in Excel, you never need this stuff, you can just use helper columns and PivotTables and SUMIFS functions : )