repeating sequence in Numbers table

Hi Brian,

Hi Brian,

"(they are not necessarily adjacent)" could toss a spanner in the works, depending what goes in B2, D2, F2, etc.

Can you supply any information regarding the contents of those cells?

If the intervening cells are to be left "empty" or will have a fixed value, this should work:

A2: 1

B2: empty

C2: IF(MOD(COLUMN(),2)=0,"",CHOOSE(A2,2,3,1))

Select C2, then fill right as far as needed.

Regards,

Barry

Hi Brian,

Here's how it works:

C2: IF(MOD(COLUMN(),2)=0,"",CHOOSE(A2,2,3,1))

Formulas work left to right, aand from the innermost set of parentheses to the outermost set.

COLUMN() returns the number of the column containing the formula. For the first copy of this formula, COLUMN returns 3

MOD(a,b) Divides a by b and returns the remainder. For Column C, and all other odd numbered columns, the remainder is 1. For even numbered columns, the remainder when divided by 2 is 0.

IF has three parts, separated by commas: if-expression,if-true,if-false

if-expression is MOD(COLUMN(),2)=0

The expression will return TRUE for even numbered columns, and FALSE for odd numbered columns.

if-true, IF places a null string ( "" ) in its cell, and exits.

if-false, IF calls CHOOSE

CHOOSE has several parts: CHOOSE(index,choice1,choice2,…)

Index is an integer. In C2, the integer in cell A2, two cells to the left of C2, is one ( 1 )

choice 1 is the integer 2, so the formula returns 2 in cell C2.

A2 'relative' reference, so as the formula is filled right into other cells in row 2, the column part of the reference will increment to remain on the cell in the same position, relative to the cell containing each copy of the formula.

In E2, the formula will get the 2 in cell C2, and CHOOSE will choose choice 2 (which is the number 3)

In G2, the formula will get the 3 in cell E2, and CHOOSE will choose choice 1 (which is the number 1), beginning the sequence again.

Regards,

Barry

Hi Brian,

So that I can enter any number 1-3 in any give cell and the sequence continues 1,2,3,1,2....

I misunderstood your aim. I thought you meant any cell, not just a cell in column A.

Here is another way. The values in Column A show how the sequence can be kicked off.

=IF(A2≤2,A2+1,1)

And I just discovered that Numbers 5.0.1 will automatically adjust that particular formula (once they are all entered) if you decide you need extra blank columns (and even if you delete blank columns).

I have not tested this in previous versions of Numbers.

Regards,

Ian.

HI Brian,

So that I can enter any number 1-3 in any give cell and the sequence continues 1,2,3,1,2....

If you enter a value in a cell containing a formula, the formula will be replaced by that value.

Here is a method where you insert a number in another row to restart the sequence.

First, the basic 1, 2, 3 pattern

A2 contains 1, typed in

Formula in C2=IF(C3="",IF(A2≤2,A2+1,1),C3)

Select cells B2 and C2 and fill right.

Now to restart the sequence as 1 or 2, type 1 or 2 in row 3

Regards,

Ian.

HI Brian,

Regarding the "Solved" checkmark and the "Helpful" stars:

As the OP (person asking the question), you get one Solved and two Helpfuls to use as you see fit in marking responses to your questions.

The main purpose of these markers is to assist those who come along later with a similar question to yours and do a search for answers. Results of the search give the question that was asked, and show the green check is the OP has marked a response as having Solved the issue or the red star if any response has been marked Helpful.

The markers also move the marked posts to the top of the thread, so that anyone clicking on the link to the question sees the question, immediately followed by the post marked Solved, then by any posts marked Helpful.

Useful to the searcher if the "Solved" post actually contains the solution to the issue; not so much if it's the one that says "That did the Trick!" leaving the searcher to search through the discussion to find the post that actually holds the answer.

Regards,

Barry