Excel BYROW and BYCOL Functions (Two Examples)
The Excel BYROW and BYCOL function apply a LAMBDA function to each row, or column, of a given array.
Advanced Excel Formulas book OUT NOW
amzn.to/3Rg87Go
These functions can help us take our data analysis in Excel further by enabling an aggregation such as sum or average, to be performed on each row or column of a range, and return one array.
This video shows two examples. An Excel BYROW function example followed by an Excel BYCOL function example.
Look out for uses of FILTER, VSTACK, TRANSPOSE, SORT, COUNTA and N also.
Download the sample file
computergaga.com/_excel/files...
Video timings
00:00 Introduction to BYROW and BYCOL
00:39 BYROW function example
04:08 FILTER with the BYROW function
05:03 Count matching values between two columns
06:56 BYCOL function example
08:26 Adding VSTACK to BYCOL
10:10 Adding TRANSPOSE and SORT
Find more great free tutorials at; www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / alanmurray-computergaga
Instagram ► / computergaga1
Twitter ► / computergaga1
#advancedexcelformulas #excelformulas #excelfunctions
Пікірлер: 28
Really nice examples thanks Alan. The problem for us older Excellers is remembering we can do these unimaginable things by array formula now and not with intermediate steps or VBA.
@Computergaga
Жыл бұрын
Yes, there are so many options. Array formulas incredible.
Clear and right to the point ,Thanks Alan!!!!
@Computergaga
Жыл бұрын
You're welcome, Izzat. Thank you 😊
Very clear and well explained. Thanks Alan❤
@Computergaga
Жыл бұрын
You're very welcome. Thank you 😊
Very nice examples! thanks!
@Computergaga
Жыл бұрын
You're welcome, thank you.
A very complete material. Thank you Alan!!
@Computergaga
Жыл бұрын
😊 Thank you Iván.
@electro_sykes
Жыл бұрын
@@Computergaga you sound so boring, mate. But other than that, good advice 👍
Thank you Alan. May try them for myself!
@Computergaga
Жыл бұрын
Go for it Mark
Nice, thank you Alan.
@Computergaga
Жыл бұрын
Thanks, Luciano 👍😊
Thanks mate! I am still reading your briliance book with a lot of pleasure!
@Computergaga
Жыл бұрын
Thanks Bart. Good to hear 😊
@electro_sykes
Жыл бұрын
@@Computergaga you sound so boring, mate. But other than that, good advice 👍
Dear Respected Sir, I am one of your big fan on social media, the way you teach is very amazing and easy to understand. Can you please upload full course for Power BI on your channel or website. Thanks
@Computergaga
Жыл бұрын
Thank you very much, Safdar. I have a Power BI book bit.ly/in-pbi-book But no course as yet.
@razianrazian9778
Жыл бұрын
@@Computergaga Thank you Alan, I just bought your book online and have started reading. Thanks
Nice tricks Alan. I am not sure yet how to categorize these functions; whether they belong to curiosities or are truly useful! I had a student come to me recently with a fun challenge assignment that looked like this: =LAMBDA(MyCalc,LET(avg,BYCOL(DataSet,LAMBDA(col,AVERAGE(col))),min,BYCOL(DataSet,LAMBDA(col,MIN(col))),max,BYCOL(DataSet,LAMBDA(col,MAX(col))),row_headers,{"average";"minimum";"maximum"},result,HSTACK(row_headers,VSTACK(avg,min,max)),result))(DataSet). The output is a three-row, multi-column report with average, minimum, maximum of a data set all in one single formula. I think some financial people or consultants may cringe when seeing such formulas if they ever need to be audited!!🤣 But they are pretty efficient!
@Computergaga
Жыл бұрын
Thanks for sharing Yves. Nice!
@YvesAustin
Жыл бұрын
@@Computergaga gave me a headache for a bit trying to figure it out! Lol
@electro_sykes
Жыл бұрын
@@Computergaga you sound so boring, mate. But other than that, good advice 👍
Simple byrow and bycol function returns na error. Plz help
n( ) formula not working, I use -- ( ) instead.
@Computergaga
4 ай бұрын
Nice work 👍🏼