Using BYCOL or BYROW is very clearly illustrated here. However, how would you go about to create a single cell formula say in cell E3 that takes arguments from both the columns with BYCOL (week headers) and the rows with BYROW for the names? The result would be an mxn matrix with results per cell being calculated based on the top row and the leftmost column. Is that possible?
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!
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
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.
Using BYCOL or BYROW is very clearly illustrated here. However, how would you go about to create a single cell formula say in cell E3 that takes arguments from both the columns with BYCOL (week headers) and the rows with BYROW for the names? The result would be an mxn matrix with results per cell being calculated based on the top row and the leftmost column. Is that possible?
Simple byrow and bycol function returns na error. Plz help
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!
Thanks for sharing Yves. Nice!
@@Computergaga gave me a headache for a bit trying to figure it out! Lol
@@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
Thank you very much, Safdar.
I have a Power BI book bit.ly/in-pbi-book
But no course as yet.
@@Computergaga
Thank you Alan, I just bought your book online and have started reading.
Thanks
n( ) formula not working, I use -- ( ) instead.
Nice work 👍🏼
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.
Yes, there are so many options. Array formulas incredible.
Thanks mate! I am still reading your briliance book with a lot of pleasure!
Thanks Bart. Good to hear 😊
@@Computergaga you sound so boring, mate. But other than that, good advice 👍
Clear and right to the point ,Thanks Alan!!!!
You're welcome, Izzat. Thank you 😊
Thank you Alan. May try them for myself!
Go for it Mark
Very clear and well explained. Thanks Alan❤
You're very welcome. Thank you 😊
Very nice examples! thanks!
You're welcome, thank you.
A very complete material. Thank you Alan!!
😊 Thank you Iván.
@@Computergaga you sound so boring, mate. But other than that, good advice 👍
Nice, thank you Alan.
Thanks, Luciano 👍😊