Serpentine numbers in a table formula

I have tried to google 'how to serpentine numbers excel formula' without any luck. I also replaced serpentine with zigzag and did not receive the results I was looking for.

This will be used as a field map for research plots on a experimental farm. I'm a student please help.

I want to use a formula to add numbers sequentially to a table in a serpentine manner (see below and/or attachment). Basically, the numbers will begin normally and use excels automatic function (1, 2, 3, etc...-->). Afterwards, the numbers should continue to add numbers sequentially on the next row, except in the reverse order (<--...etc, 13, 12, 11).

1 2 3 4 5 6 7 8 9 10
20 19 18 17 16 15 14 13 12 11
21 22 23 24 25 26 27 28 29 30
40 39 38 37 36 35 34 33 32 31

I hope this makes sense and thank you in advance.

Another option if you have the Lambda function

Formula:

Please Login or Register  to view this content.

where B1 is the number of rows & B2 the number of columns

Forgot to change part of the formula to make it dynamic.
It should be

Formula:

Please Login or Register  to view this content.

Hello & Welcome to forum

Could this work for you? You would need to copy it for every new row.

Thank you all for the quick reply. These are all really good answers.

I think this formula will work for my immediate purposes. See attachment Sheet2.

=SEQUENCE(B1,B2)+ISEVEN(SEQUENCE(B1))*SEQUENCE(,B2,B2-1,-2)

However, I really wanted to get this code to work; as I could manipulate other files that are already made. See attachment Sheet1.

=J1+IF($A1=MAX($A1:$J1),(J$1*2-1),20-(J$1*2-1))

The problem with this formula is the [value if true] and [value if false] statements in the IF formula. This formula is based on my generalized table beginning with the number 1. However, the sample numbers will never begin with 1 (more likely 1001). Likewise, most fields will not be that simple; some may have 15 rows and 23 columns, or any number.
I'm trying to adjust the formula to continue the serpentine sequential numbers, regardless of the beginning number and/or number of rows or columns.

I have another problem, but isn't critical. These answers are replying to my example table that begins in the top left and proceeds top to bottom. However, I was just informed that it is standard practice in field research to begin in the bottom left and proceed bottom to top (See attachment Sheet3). I can reverse order the tables (See attachment Sheet4 and formula below), but I would like it if I didn't need the extra step.

=SORTBY($A$1:$J$4,ROW(A1:A4),-1)

Using sheet3 & the formula I suggested you can sort it like

Formula:

Please Login or Register  to view this content.

Hi again!

I put two new options, with your new requirements.
Option 1 - Dynamic arrays

PHP Code: 

=SEQUENCE(C2,C3,C4+C2*C3-1,-1)-ISEVEN(SEQUENCE(C2))*SEQUENCE(,C3,C3-1,-2) 


Option 2 - Old school formulas

PHP Code: 

=IF(OR(ROWS(B$7:B7)>$C$2,COLUMNS($B7:B7)>$C$3),"",$C$4+$C$3*($C$2-ROWS(B$7:B7))+IF(ISEVEN(ROWS(B$7:B7)),COLUMNS($B7:B7)-1,$C$3-COLUMNS($B7:B7))) 


This option drag it down and right. Check file. Blessings!

A tweak to the one you posted above, removing the need to hard-code the size of your table. Put this in A2 of the file you uploaded in post #8 and fill across/down:

=IF($A1=MAX($A1:$J1),$A1+COLUMNS($A1:A1),$J1+COLUMNS(A1:$J1))

Works whether you are going up or down (obviously if starting at the bottom then you would change the 1s to 3s if putting in A2, but you can copy it easily enough)

Hi. I think Nick's tweak will do the job.

My current formula has certain limitations, such as starting number must be 1 and table must be at 10 cell width. Admittedly I did work on the example you provided, but it is entirely possible to remove those limitations with COUNT and maybe COLUMN. I have to work on it, but my home computer doesn't have Excel to I can only take a look tomorrow.

That is unless someone else doesn't do it till then

Hello again!

Here's the reworked version of the formula. You still have 2 limitations: 1) you have to enter the first row of values manually, since there would be no way of knowing where, what, and whether descending or ascending order the beginning numbers will be, and 2) when you copy the formula for a new data, you need to manually adjust the formula references. Like for example if your data is from C to F, you need to change the A:D references in the formula to C:F. You would only need to do this in 1 cell, because when copied to the rest of the data it will arrange itself.

I have presented some examples in the attached file. Please check & let me (or us ) know if that works!

Regards

Wow, thank you very much; you people are awesome.

I really like Nicks solution, as its a formula I can use in any excel sheet. However, MGC's solution works great for starting a new file.

I find it hard to believe that I was unable to search for a solution on the internet. Field research has been using this serpentine method forever. There are some software packages that will create field maps automatically. This software will allow 'serpentine' or 'typewriter' sequential numbering. These software also allow you to start in any position (Top or Bottom; Left or Right). We don't have the budget for additional software and excel should have an easier solution for this problem. Anyways, thank you so much for all your help