need series with repeating value

[ANKUSHRS1]

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

 

[NARAYANK991]

Hi ,

See if this formula works :

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

Enter this in any cell , and copy down.

Narayan

 

[Peter Bartholomew]

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.

 

[NARAYANK991]

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.

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

 

[Peter Bartholomew]

@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.

 

[NARAYANK991]

@NARAYANK
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.

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

 

[Peter Bartholomew]

… 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?

 

[Lori]

Where was it that the UserVoice request for =SEQ(n) was discussed?

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.

 

[Peter Bartholomew]

@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.

 

[Lori]

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)]

 

[Lori]

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...)

 

[Peter Bartholomew]

@Lori

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

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.

 

[Lori]

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...

 

[GraH - Guido]

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.