Reputation: 25
I wanted to run multiple sequences of numbers in a single column in excel sheet. For example, this is how a sample output should look like :
As you can see, there are two separate sequences of numbers that are running in single column. One starts from "1" and another starts from "100". What formula can I write in excel such that excel leaves a gap of two rows after "1" and "2" and continues to "3" and "4" and so on.
In this example, I have shown two sequences of numbers which are running in a single column, how can I extend that for 3 or 4 sequences etc.
Upvotes: 1
Views: 465
Reputation: 34440
Here is a draft of how it might look. Basically taking the row number and looking up the corresponding start value, but subtracting out the terms belonging to the other series (two in this case) and subtracting out the rows before it in the current iteration of the series:
=INDEX(A$2:A$4,MATCH(MOD(ROW()-2,9),C$2:C$4))+ROW()-2
-QUOTIENT(ROW()-2,9)*SUMPRODUCT(B$2:B$4*((ROW(B$2:B$4)-1)<>MATCH(MOD(ROW()-2,9),C$2:C$4)))
-INDEX(C$2:C$4,MATCH(MOD(ROW()-2,9),C$2:C$4))
Can be developed into a spill formula in O365 and tidied up removing the helper column and the hard-coded '9' and using Sequence and Let.
So the whole thing in O365 would be
=LET(startRange,A2:A4,
countRange,B2:B4,
cumTotal,MMULT(IF(ROW(countRange)>TRANSPOSE(ROW(countRange)),1,0),countRange),
rowSeq,SEQUENCE(100,1,0),
totCount,SUM(countRange),
modRow,MOD(rowSeq,totCount),
series,MATCH(modRow,cumTotal),
startValue,INDEX(startRange,series),
countForSeries,INDEX(countRange,series),
quotient,QUOTIENT(rowSeq,totCount),
subtract1,quotient*(totCount-countForSeries),
subtract2,INDEX(cumTotal,series),
startValue+rowSeq-subtract1-subtract2)
Upvotes: 1