SEQUENCE Function

Krupin Dharaiya Put the formula accordingly:

• in K4: =I3 • in L4: =K4+9 • in N4: =L4+1 • in O4: =N4+9 • in Q4: =O4+1 • in R4: =EOMONTH(I3,0)

• in H6: =SEQUENCE(COUNTA(I6:I100)) • in I6 =FILTER(B6:B21,A6:A21<=EOMONTH(I3,0)) • in L6: =SUMIFS($C:$C,$B:$B,$I6,$E:$E,">="&K$4,$E:$E,"<="&L$4) • in O6: =SUMIFS($C:$C,$B:$B,$I6,$E:$E,">="&N$4,$E:$E,"<="&O$4) • in R6: =SUMIFS($C:$C,$B:$B,$I6,$E:$E,">="&Q$4,$E:$E,"<="&R$4)

You could try as Petr posted or you could use SEQUENCE Function =SEQUENCE(NoOfRowssYouNeed,,1,8)

=SEQUENCE(50000,,1600001,1)

Md Rajak Sk I will highly suggest you to check the link in Microsoft Website on how to use a SEQUENCE() function

https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90

Md Rajak Sk it seems you are using MS365, then use a formula with SEQUENCE(). Or, watch below how to get the sequence using the series fill.

• =SEQUENCE(165000,,160001)

Na célula G2, coloca a seguinte fórmula:

=ARRAYFORMULA(SE(D2:D="";"";PROCV(D2:D;{{0;SEQUENCE(9;1;19;5)}'Página1'!$B$3:$C$12};SE(F2:F='Página1'!$B$2;2;3);1)))

obs: Substitui o nome 'Página1' para o nome da aba onde está sua tabela 1 - a que recebe as idades. Ou se as tabelas tiverem na mesma aba, apenas remova o 'Página1' da fórmula.

Jackson Mark here are three approaches; you can use either of the one. With VSTACK() & LAMBDA() it is not required for your query, however if you have larger range then you can find the solution with dynamic spill array in the link below I have already posted this.

https://stackoverflow.com/questions/74181973/is-there-a-non-vba-excel-spilling-formula-to-create-and-process-arrays-of-arrays

• Formula used in cell B4

=EXPAND(B2,B1,,B2)

Or,

• Formula used in cell B8

=XLOOKUP(SEQUENCE(B1),SCAN(0,B1,LAMBDA(a,b,a+b)),B2,,1)

Or,

• Formula used in cell B12

=TEXTSPLIT(REPT(B2&"|",B1),,"|",1)

try: =LET(range,TOROW(D2:BJ2,3),@LEN(TEXTSPLIT(TEXTJOIN("",,(INDEX(range,-SORT(SEQUENCE(,COLUMNS(range),-1,-1),,,1))="PT")/1),0)))

So would you want us to guess what is in cell I6?

I know that pero Ang Dami na nila siguro sapat na yan para maayos mo🙂

May Be =MID(A2,LOOKUP(8^5,FIND(TEXT(SEQUENCE(,12,1)*29,"mmm"),A2)),12)

The formula you show will not produce the results you have shown in the screen shot. also, if you enter a formula in C4 that references C4 you will get a circular reference error, and these should be avoided. IF you use dynamic array, they are CONTIGOUSE cells only. you cannot put bank rows/column in the range.

Depends what you want to do with ties, this will give the 3 lowest with no ties, remove the UNIQUE to list ties

=SMALL(UNIQUE(A:A),SEQUENCE(3))

will it be 500 for march even if lets say the end date is mar 10th. Is march still considered whole month for the calculation at that point or the amount needs to be distributed for just 10 days?!

=TOCOL(SEQUENCE(51,,1000)&"-"&SEQUENCE(,4))

For below <50 =IFERROR(ARRAYTOTEXT(FILTER(C$3:C$11,($B$3:$B$11=$F4)*($D$3:$D$11<50))),"")

For Above >50 =IFERROR(ARRAYTOTEXT(FILTER(C$3:C$11,($B$3:$B$11=$F4)*($D$3:$D$11>50))),"")

Also, =HSTACK(TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,2),TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,-1))

In row 6 type this =A1+1

And drag it to end how much you required

Perhaps, if it's sequence(), divide the result by 10 ..ex =arrayformula(sequence(20,1,11,1)/10).

If not a sequence try MUNIT() with sequence()

Here you have all 3x Solutions without VBA from "Comments" ... . But I am sure that UDF made by Dave Houston will also work for you . If number for Sequence never will be higher than 5, maybe you can use Nested IFs or IFS Function