1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need series with repeating value

Discussion in 'Ask an Excel Question' started by ANKUSHRS1, May 27, 2018.

  1. ANKUSHRS1

    ANKUSHRS1 Member

    Messages:
    56
    Hii,

    all I need the formula to series with repeating value like below

    1
    1
    1
    1
    2
    2
    2
    2
    3
    3
    3
    3

    as above example in repeat the series 4 cell.
    pls note that I want this formula in same column (not supporting coloumn) as I want to add this in my present formula
    pls help
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See if this formula works :

    =INT((ROW(A1) - 1)/4) + 1

    Enter this in any cell , and copy down.

    Narayan
    Thomas Kuriakose likes this.
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    Just to show a different way of working …
    I dislike the concept of direct cell referencing (A1 etc) so I use Names and, as I prefer not to over-use relative referencing, I tend to use array formulae.

    Given a column array called 'array' with a heading called 'heading' it is possible to define a record number 'recordNumber' using the Named formula

    {= ROW(Array) - ROW(Heading)}

    Then your formula can be defined to refer to

    {= CEILING( recordNumber, 4 ) / 4}}

    So far this 'formula' is just a recipe for a calculation. It might make sense to enter it into the array

    {= formula}

    to evaluate it but then again, you could just use it in another formula, e.g.

    = STDEV.P(formula)

    which, for an array of length 20, would give √2.
    Thomas Kuriakose likes this.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    But surely when we use ROW(A1) we are not using cell referencing !

    ROW(A1) is used solely because it can be copied down a column to return the series 1 , 2 , 3 ,....

    The formula is simple maths entered as an Excel formula.

    Narayan
    Thomas Kuriakose likes this.
  5. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    @NARAYANK
    OK, I will concede that point! In offering alternatives, I am not attempting to criticise the solutions you and others offer; I admire them and they communicate the ideas to spreadsheet users. It is Dan Bricklin's original decisions regarding spreadsheet nomenclature that do not suit me - especially so since his reason for not setting things up "the programmers' way" was that it would be "tedious".

    I am not even that impressed by the ROW() function because it only returns a location relative to the sheet; something I never have the slightest interest in. I would prefer

    = ROW(reference, relativeTo)
    which would make it far easier to apply an index to a range.
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    But the usage of ROW(A1) is merely a variable which refers to the number 1 , so that when it is copied down a column , Excel increments it automatically , so that we can get the same variable to refer to the numbers 2 , 3 , 4 ,...

    It is certainly not referring to the worksheet cell A1.

    Where ever the function ROW(A1) is used , it will refer to the number 1.

    I agree that the function ROW() will return the row number in a worksheet , and is thus a worksheet reference.

    Narayan
  7. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    … so, the A1 is just a red herring; the formula has nothing to do with cell A1.
    Until, the user inserts a new top row that is; then it turns round and bites you.

    Yes, I do agree it is standard practice, it is concise and an experienced spreadsheet user should be able to repair the workbook. Where was it that the UserVoice request for =SEQ(n) was discussed?
    NARAYANK991 likes this.
  8. Lori

    Lori Active Member

    Messages:
    161
    Hi Peter, That discussion was raised in your last challenge thread.

    Some options that allow for row insertions at the top are:
    • Paste as values (for a static list)
    • ROWS($A$1:A1) filled down from A1
    • ROW()-MIN(ROW())+1 in any vertical range with CSE
    • Table references (like in your suggestion)
    Being something of a purist, i try not to reference any cells outside input data. For example using ROW(1:100) will make a formula recalculate whenever any cell in the top 100 rows changes.
    Thomas Kuriakose likes this.
  9. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    @Lori

    Thanks for the reminder; I simply voted and moved on :(

    I wasn't familiar with your third option. It even works within a named formula. It is crazy that there is no purpose-built function for such a basic operation.

    A variant that I have used for a bounded number sequence is
    thisColumn: = C [assuming R1C1 notation]
    seq: = ROW( INDEX( thisColumn, 1 ) : INDEX( thisColumn, n ) )

    As far as I can tell, it cannot be accidentally corrupted by deleting rows or columns and is at least moderately sparing in the precedent cells it creates.
  10. Lori

    Lori Active Member

    Messages:
    161
    Yes, your 'seq' definition should be fine in general. Only deleting all cells on sheet will affect it.

    A possible modification that removes any additional cell precedents and sensitivity to worksheet operations is:

    thisColumn:=!C

    [This syntax has caveats but is ok when used in conjunction with ROW() - it points to the current sheet like in CELL("row")]]

    Another option is to work with sufficiently large static arrays that may be easily defined from the immediate window:

    Names.Add "seq", [row(1:1024)]
    Thomas Kuriakose likes this.
  11. Lori

    Lori Active Member

    Messages:
    161
    Actually, the alternative syntax still creates column precedents they just don't show in the formula tools. And for the static array one can use [r1:r1024] which allows A1 or R1C1 style refs.

    It's all a bit of a faff though. The proposed SEQ function needs more votes! (9 so far...)
  12. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    @Lori
    Well, that is a first for me. I do not think I have ever programmed a workbook formula from the immediate window before!

    I have programmed a workbook from VBA though. In a workbook that was restricted to array formulas (and those always placed within named ranges), I read and documented every formula within a table from VBA. The party-piece was to delete the formulas and then to rebuild a working model from a blank workbook together with the documentation.

    What I generated from your code line was an array constant that contained no worksheet references.

    I do occasionally wonder about your background. Either you are extremely innovative and curious or you would appear to have some professional IT background. You appear to have ideas and knowledge that make the normal 'tips and tricks' blogs appear childish! Thank you for sharing.
    NARAYANK991 likes this.
  13. Lori

    Lori Active Member

    Messages:
    161
    Well, i'm flattered but many of those ideas i can't claim really as mine as they build on work of many others. I learned much from the book "Excel Expert Solutions" back in the 90s that compiled many tips from MVPs of that era - still available from Amazon but now totally out of date.

    I've been a (quantitative) developer for some years but continue to hold an interest in what can be achieved within the standalone office application in part due to it's universal availability.

    And there's always lots of new things to learn through the sharing of ideas with the many other knowledgeable members on forums like this one - even in areas of the application one feels one knows quite well...
  14. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Don't be too modest, Lori, it is your merit that you bring that knowledge alive, applicable in other cases and therefore to good use. It requires insight and creativity to do so.
    Last edited: Jun 1, 2018
    NARAYANK991 likes this.

Share This Page