ETA-LAMBDAs for BYROW, BYCOL & SCAN to Make Spilling Row Totals & Running Totals Easy. EMT 1846
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1846....
Learn about how to use ETA-LAMBDAs to replace LAMBDA to make easier formulas to spill row totals, spill column totals, spill running totals and even spilling an average in each row.
Topics:
1. (00:00) Introduction.
2. (00:18) Spill Row Totals with the BYROW function.
3. (00:50) Spill Column Totals with the BYCOL function.
4. (01:06) Spill a Running Total with the SACN function.
5. (01:32) Spill an Average for each row with the BYROW function.
6. (01:53) Summary.
7. (02:12) Closing, Video Links For Full Free LAMBDA Class.
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lambda #excellambda #byrow #bycol #scan #spillingthemilk #array #arrayformula
![XO (Only If You Say Yes) - ENHYPEN エンハイプン 엔하이픈 [Music Bank] | KBS WORLD TV 240712](http://i.ytimg.com/vi/wBT1bKKwPTo/mqdefault.jpg)
Another great tip! Using arrays means less formulas and less risk of error. Thank you!
Unbelievable how much more user-friendly complex Excel tasks have gotten the past few years... Still very glad I learned the old formulas from you (or how to copy/paste them) so I can really appreciate the way Excel is evolving. Thank you Mike!!!
Yes indeed, you are wise to study the evolution: it makes us much better Excel Users : ) : ) : )
Yes,it should have been Eta from the get-go! Eta: a helper for the LAMBDA Helpers! Eta's have been around in Lambda Calculus for a long time. Now they come to Excel!! This development will GREATLY expand the use of these LAMBDA Helper Functions in doing routine calculations.
100% agree: expand to a higher infinity : ) : ) Love this: Eta: a helper for the LAMBDA Helpers!!!
BTW The english language pronunciation of "Eta" mimics or rhymes with "Beta". The "E" is pronounced as a long "A" as in "ate" and the "a" at the end is pronounced like the "u" in "upper". (That's they way I've always said/heard it but I also confirmed it with the Ancient Language Institute--yes there really is such a thing!! LOL But, according to the ALI msome Europeans including most Greeks today pronounce it with a very long "E" as in "eat"!)
@@excelisfun yeah, a helper's helper; like the housekeeper who brings a robo-vac to your house and stares at Facebook while the robot has all the fun!!!
Nice to see this coming from Microsoft for SCAN which is usually not too intuitive from an understanding perspective.
I 100% agree : ) : )
Brilliant as always Mike, thanks
You are welcome!!!!
How we "used to do it" 99.99% of Excel users have never used LAMBDA yet. I'm an Excel Trainer and I'm having a hard time keeping up while teaching.
Yah, that is a funny line... How we used to do it is just for last year lol
I am now understanding the power of ETA LAMBDA
: ) : ) : )
I didn't know that Lambda can no longer be used. Aweosme change by Microsoft. Thank you Mike for this epic video 📹 👍
LAMBDA can be used, but the new ETA LAMBDAs can be used also.
You still need lambda to find the nth largest Value of each row
An example
=Byrow(A1:C30,Lambda(x,Large(x,3)))
@@Ferhatt399 Very nice!!! : )
Beautiful. Thanks, Mike.
You are always welcome : ) : ) : )
Just tested these this morning. Perfect. Could we create our own ETAs? Maybe one day!
That will be a good day.
Mike, Super Great Running Total trick, right there!
Finally we don’t have to use expanding ranges to SUM, which were not so efficient.
SCAN + etas are the MAX!! :-)
I love this: SCAN + etas are the MAX!! To The Max and Beyond...
Thanks Mike. This is Great!!! :) :)
Yes, MS is soooooo good to us : ) : )
Wow, Thanks MS for bringing new updated functions; may I know these will be available for the users.
Nice content...sir
Glad you like it!!!!!
Hi Mike, that spilling running total is now so easy to do now with these ETA Lambda's.
You can do a spilling running total with just SUMIFS with a little work around. But no need anymore, much easier with these new functions. Can't wait to be released to everyone.
Did your PERCENTOF also disappear? Notice it was not part of the list of ETA Lambda's.
Yes, Crazy: PERCENTOF was here for first two days and now it is gone. My guess is that becasue it only worked on SUM and not with other important aggregate calcualtions like COUNT and COUNTA, they had to go back to drawing board. But I almost always guess wrong lol
@@excelisfun thanks for the info about PERCENTOF and this great tutorial as well!💯
@@tibibara You are welcome!!!
What does ETA stand for?
First off, ETA is the name of a Greek letter. My phone can't make one (much thanks Samsung/ Google) but it looks like a lower-case "h" with a long curvy right tail. In this instance it refers to "ETA-reduced LAMBDA Fonction. In very rough terms a function like BYROWS or SCAN that comtains a LAMBDA function within first
calculates BYROWS until it needs the LAMBDA function. BYROWS then passes its results to LAMBDA to do its calculation.
LAMBDA finishes its part of the puzzle and hands the project back to BYROWS. If every thing is hunky-dory BYROWS finshes the job and unleashes the Big Spill down the column. An ETA reduction is an overlay algorithm the tells BYROWS to delay all of its work and hands the project immediately to LAMBDA. BYROWS then takes the results of LAMDA and then does all its workat once. This can result in more efficient processing and fewer instructions (variables) in the LAMBDA sub-function. The word "reduction" in ETA-reduction refers to the elimination of the first step of the BYROWS calculation.
@@richardhay645 Like the Word Poet Richard Hay said : ) : ) : ) : ) : )
Pls can you help me .... I have Office 365 BETA channel but not Groupby, ... and ETA-LAMDAS? Why?
It should be out to all beta in about a week.
Wish I could get a sequence in the function to unstack numbers to a sequence of numbers...it will have different size columns, but all in a single array...
I am sorry... : ( I am not sure what you are asking. However, other Teammates may be able to help : )
At 1:03 are you saying ctrl+enter? Is it necessary?
No. But Ctrl + Enter takes an item from Edit Mode and puts it in the cell and keeps the cell selected. Since I am editing the cell over and over, it is better than clicking Enter and then always having to do an unnecessary click to get back to cell.
@excelisfun , under File /Options, you should change the section to 'do nothing after pressing Enter'. That way the cursor won't move after invoking a formula.
Problem is a lot of people still eont have been upgrsded to 365 yet do their is huge global compatability issues
Yes, that is a BIG problem. Where I work, many people do not have it... : (
It's not that different to when office went from 2003 to 2007
1st comment
First Place Trophy for you : ) : ) : )
@@excelisfun Is there a way to equate 3 or 4 or more cells, like 2 = 2 = 2, it should evaluates to TRUE, but it returns FALSE. Excel Problem!
@@Al-Ahdal A formula like this might work:
=AND(D6=E6,E6=F6)
@@excelisfun Sir, please guide Microsoft to remove bugs like.... 2 = 2 = 2 cannot be correctly evaluated to TRUE, and returns FALSE. Moreover, there are certain bugs which you yourself highlighted in your one video, like isblank etc etc. Kindly do comment on this, and share vdo link here. Thank you
@@Al-Ahdal That has been a problem since the beginning. I usually use AND and AND functions for that or MATCH or any array operation. There are other tricks also to accomplish that goal. I have no power to influence Microsoft, even though I am an MVP. They always do what they want.
0:42 👈 Sum = Lambda(x,Sum(x)) ?
Nicely summarized : )
@@excelisfun Is there any difference in performance between Sum and Lambda(x,Sum(x))
@@Ferhatt399 I did not test it, but I would guess that they have the same performance.
@@excelisfun Thank you very much, stay healthy