How to make REPEAT formula in excel using LAMBDA
ฝัง
- เผยแพร่เมื่อ 5 ต.ค. 2024
- Create a formula to get repeating numbers: either 1,2,3,1,2,3 or 1,1,1,2,2,2
Using LAMBDA and SEQUENCE: new formulas in Office 365 Beta Channel
=IF(B10=0,MOD(SEQUENCE(B4*C7,1,0,1),B4)*B7+C4,QUOTIENT(SEQUENCE(B4*C7,1,0,1),B4)*B7+C4)
=LAMBDA(repeat,start,step,stop,type,IF(type=0,MOD(SEQUENCE(repeat*stop,1,0,1),repeat)*step+start,QUOTIENT(SEQUENCE(repeat*stop,1,0,1),repeat)*step+start))(3,1,1,3,0)
---------------------------------------------------------------------------------
#exceltips #exceltutorials #excelformulas
SUBSCRIBE: goo.gl/c46YPs
Microsoft Office 365, Beta Channel, Version 2106
---------------------------------------------------------------------------------
IF YOU LIKED THIS VIDEO YOU MAY ALSO LIKE:
How to autofill numbers in excel • How to autofill number...
How to get repeating numbers in excel 1,2,3,1,2,3 • How to get repeating n...
How to get repeating numbers in excel 1,1,1,2,2,2 • How to get repeating n...
Wonderful work! I want to suggest to substitute a "repeat" with a "stop" in the type 1 formula so that the result is "more correct".
If I use REPEAT (3,1,1;2;0) I get 1,2,3,1,2,3 while if I use REPEAT(3,1,1,2,0) I get 1,1,2,2,3,3 ... so in both case I repeat the first 3 elements 2 times, starting from 1 and with a pace of 1
=LAMBDA(repeat,start,step,stop,type,IF(type=0,MOD(SEQUENCE(repeat*stop,1,0,1),repeat)*step+start,QUOTIENT(SEQUENCE(repeat*stop,1,0,1),stop)*step+start))(3,1,1,3,0)
Hello Many thanks for this video,you have no idea how much this video help me.
From France, thanks, it's elegant
Brilliant! Thank you!
in love with excel ❤️
Is there a way to limit the quotient plus sequence formula? Type 1.
For example I need 111,222,111,222
Instead of 111,222,333,444
You are amazing
Thanks for the help, your name includes the word cock which is funny