Opps, the formula in the video will not pick out a mode in quartile 4 range if the max value is also the mode (occurs infrequently, but can happen). In that case, we would have to amend formula like this: =TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=J10)*(IF(I11=4,$B$3:$B$36
Good video, Mike! At 4:14 where you wish to select B3:B36, you needn't click and slide with the i-beam; just double click any range like that one and it will select only the column/row/colon combination. Bonus tip: when you know that a parenthesis is required at the end of the formula (like at 6:43), you needn't bother moving the cursor to the end and adding it; just press enter, there will be an error message that defaults to Yes, so just press enter again without even reading it and your formula will invoke. So, Enter Enter is quick!
Great video!!✌ A single cell formula that also spills horizontally, a single variable, the initial array: =LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1), REDUCE(0,SEQUENCE(4),LAMBDA(v,i, LET(x,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IF(SEQUENCE(i)=i,y,v))))) BYROW can not spill (horizontally and/or vertically), but, with REDUCE, we can make any array to spill "byrow" style, for any function that spills horizontally✌😉
Thank you very much. I tried a bunch of things, but no luck. I tried stuff like: =MAP(INDEX(E10#,SEQUENCE(ROWS(E10#)-1)),INDEX(E10#,SEQUENCE(ROWS(E10#)-1,,2)),LAMBDA(l,u,BYROW(B3:B36,LAMBDA(r,FILTER(r,(r>=l)*(r
DUDE!!!! This formula is heavy. I am hours in and at least I can type it out and get it to work: =LET(d,B3:B36,q,QUARTILE.INC(d,SEQUENCE(5,,0)),x,XMATCH(d,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(a,r,LET(xx,TRANSPOSE(MODE.MULT(FILTER(d,x=r))),y,IF(COLUMNS(xx)=1,IF({1,0},xx,""),xx),IF(SEQUENCE(r)=r,y,a))))) But there is all sorts of levels of action going on in this formula, the XMATCH(d,q,-1) to sections sections of quartile numbers, x variables repeated, the SEQUENCE(SEQUENCE(4)) and a bunch of other things that I don't get yet. This formula seems more invisible in how it works than DAX formulas and context transition lol But I'll keep working on it : ) : )
I am still learning with the sledge hammer method lol But this one does deal with the problem that my original formula would not list the max value in the last quartile if it was the mode: =LET(d,B3:B36,q,QUARTILE.INC(d,SEQUENCE(5,,0)), qsb,XMATCH(d,q,-1),qs,IF(qsb=5,4,qsb), REDUCE(0,SEQUENCE(4),LAMBDA(a,r, LET( m,TRANSPOSE(MODE.MULT(FILTER(d,qs=r))), y,IF(COLUMNS(m)=1,IF({1,0},m,""),m), IF(SEQUENCE(r)=r,y,a))))) Just crazy mind bending stuff, Teammate Excel Lambda ; ) Bending is learning, however : )
@@excelisfun I do remember our first talks about lambda helper functions. Turns out that REDUCE/SCAN , are , in my opinion the most powerful and versatile excel functions Ever. ✌😉
Hi Mike. Great video as usual. Thank you for the tips. I have a particular problem with Excel not being able to differentiate between two similar codes. For an instance I have the following situation. Code Amount 123456wfD 1500 123456WfD 5000 Whenever I try to utilize a lookup function the result is 1500 for both of the codes. Is there any solution to this problem?
Hi, if you set your array, for example, at B2:C3, here is a formula: =LET(lk,"123456WfD",FILTER(C2:C3,EXACT(lk,B2:B3))) returns: 5000 lk: is the lookup value
Opps, the formula in the video will not pick out a mode in quartile 4 range if the max value is also the mode (occurs infrequently, but can happen). In that case, we would have to amend formula like this:
=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=J10)*(IF(I11=4,$B$3:$B$36
Good video, Mike! At 4:14 where you wish to select B3:B36, you needn't click and slide with the i-beam; just double click any range like that one and it will select only the column/row/colon combination. Bonus tip: when you know that a parenthesis is required at the end of the formula (like at 6:43), you needn't bother moving the cursor to the end and adding it; just press enter, there will be an error message that defaults to Yes, so just press enter again without even reading it and your formula will invoke. So, Enter Enter is quick!
Whenever Mike says "BAM!!!"...
my heart soars like an unmasked hawk on hot May evening in a field of rabbits 🐇🐰🦅🦅
Wow wow wow Thank you so much Mike!!!!!!! This is super clear! This video cleared all my issues regarding this challenge! 👍👍👍👍👍👍👍
There is the problem with the max value in the last quartile section when it is the mode, that I addressed somewhat in pinned comment.
Great video!!✌
A single cell formula that also spills horizontally, a single variable, the initial array:
=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),
REDUCE(0,SEQUENCE(4),LAMBDA(v,i,
LET(x,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IF(SEQUENCE(i)=i,y,v)))))
BYROW can not spill (horizontally and/or vertically), but, with REDUCE, we can make any array to spill "byrow" style, for any function that spills horizontally✌😉
Thank you very much. I tried a bunch of things, but no luck. I tried stuff like: =MAP(INDEX(E10#,SEQUENCE(ROWS(E10#)-1)),INDEX(E10#,SEQUENCE(ROWS(E10#)-1,,2)),LAMBDA(l,u,BYROW(B3:B36,LAMBDA(r,FILTER(r,(r>=l)*(r
DUDE!!!! This formula is heavy. I am hours in and at least I can type it out and get it to work:
=LET(d,B3:B36,q,QUARTILE.INC(d,SEQUENCE(5,,0)),x,XMATCH(d,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(a,r,LET(xx,TRANSPOSE(MODE.MULT(FILTER(d,x=r))),y,IF(COLUMNS(xx)=1,IF({1,0},xx,""),xx),IF(SEQUENCE(r)=r,y,a)))))
But there is all sorts of levels of action going on in this formula, the XMATCH(d,q,-1) to sections sections of quartile numbers, x variables repeated, the SEQUENCE(SEQUENCE(4)) and a bunch of other things that I don't get yet. This formula seems more invisible in how it works than DAX formulas and context transition lol But I'll keep working on it : ) : )
I am still learning with the sledge hammer method lol
But this one does deal with the problem that my original formula would not list the max value in the last quartile if it was the mode:
=LET(d,B3:B36,q,QUARTILE.INC(d,SEQUENCE(5,,0)),
qsb,XMATCH(d,q,-1),qs,IF(qsb=5,4,qsb),
REDUCE(0,SEQUENCE(4),LAMBDA(a,r,
LET(
m,TRANSPOSE(MODE.MULT(FILTER(d,qs=r))),
y,IF(COLUMNS(m)=1,IF({1,0},m,""),m),
IF(SEQUENCE(r)=r,y,a)))))
Just crazy mind bending stuff, Teammate Excel Lambda ; ) Bending is learning, however : )
@@excelisfun Cool correction!! ✌This is bullet proof :
=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),xm,XMATCH(a,q,-1),x,IF(xm=5,4,xm),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))
@@excelisfun I do remember our first talks about lambda helper functions. Turns out that REDUCE/SCAN , are , in my opinion the most powerful and versatile excel functions Ever. ✌😉
Sir, Great session ! Respected Sir , requesting for VBA Training session
Hi Mike! Another awesome lesson. Thanks for the MODE magic :)) Thumbs up!!
Boom!Really Enjoyed This Fun Class...Thank You Mike :)
You are welcome, Mountain Biker darryl : ) : )
Mode mult+ transpose 😍superb👌❤️
Yes, shubham : ) : ) : )
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : )
Amazing!!!! Thanks Mike!!! :) :)
Awsome Mike
Thanks Mike!
You are welcome, Chris M!!!!
Awesome video
Yes!!! Glad you liked it - AND: you get first place trophy ; )
@@excelisfun Thanks
Hi Mike. Great video as usual. Thank you for the tips.
I have a particular problem with Excel not being able to differentiate between two similar codes. For an instance I have the following situation.
Code Amount
123456wfD 1500
123456WfD 5000
Whenever I try to utilize a lookup function the result is 1500 for both of the codes.
Is there any solution to this problem?
Are you asking about case sensitive lookup?
Hi, if you set your array, for example, at B2:C3, here is a formula:
=LET(lk,"123456WfD",FILTER(C2:C3,EXACT(lk,B2:B3)))
returns: 5000
lk: is the lookup value
@@Excelambda That is the formula I just created and made a video. Are you reading my mind Excel Lambda? But I did not use the LET lol
@@excelisfun Yes I believe so. Is there a way for me to get the correct result and to trick Excel's engine?
@@Excelambda Hi. Thank you very much. I will try it out :)
Aapache open office worksheet