1

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 :

enter image description here

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.

Improve this question

1 Answer 1

Reset to default
1

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.

enter image description here

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.