Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Consecutive Numbers down a column not to Exceed 49
Hi there,
I have 6 columns across going 40 rows down. I'm entering random numbers in row 1 across columns A to F. The numbers have to stay in a range from 1 to 49 but are totally random in row 1. From rows 2 through 40 I want the numbers to automatically fill down consecutively but not to exceed 49. When a certain column hits 49 I want that column to start over at 1 again. Im a total newbie when it comes to excel. I did try entering A1+1 in cell A2 and then highlighting the remaining rows down to 40 in column A. I then went to the edit menu and choosing fill €“ down. The problem here was that when I entered any number larger than 10 in row 1 I would go over 49 somewhere down the column. I need the numbers to start back at 1 when 49 is reached. A B C D E F Row 1 46 31 7 15 43 19 Row 2 47 32 8 16 44 20 Row 3 48 33 9 17 45 21 Row 4 49 34 10 18 46 22 Row 5 1 35 11 19 47 23 Row 6 2 36 12 20 48 24 Row 7 3 37 13 21 49 25 Row 8 4 38 14 22 1 26 Row 9 5 39 15 23 2 27 Thanks in Advance Nelson |
#2
|
|||
|
|||
Nelson,
Instead of =A1+1 Use =IF(A1+1=50,1,A1+1) HTH, Bernie MS Excel MVP "Nelson" wrote in message ... Hi there, I have 6 columns across going 40 rows down. I'm entering random numbers in row 1 across columns A to F. The numbers have to stay in a range from 1 to 49 but are totally random in row 1. From rows 2 through 40 I want the numbers to automatically fill down consecutively but not to exceed 49. When a certain column hits 49 I want that column to start over at 1 again. I'm a total newbie when it comes to excel. I did try entering A1+1 in cell A2 and then highlighting the remaining rows down to 40 in column A. I then went to the edit menu and choosing fill - down. The problem here was that when I entered any number larger than 10 in row 1 I would go over 49 somewhere down the column. I need the numbers to start back at 1 when 49 is reached. A B C D E F Row 1 46 31 7 15 43 19 Row 2 47 32 8 16 44 20 Row 3 48 33 9 17 45 21 Row 4 49 34 10 18 46 22 Row 5 1 35 11 19 47 23 Row 6 2 36 12 20 48 24 Row 7 3 37 13 21 49 25 Row 8 4 38 14 22 1 26 Row 9 5 39 15 23 2 27 Thanks in Advance Nelson |
#3
|
|||
|
|||
On cell A2 type
=A1*(A1<49)+1 and fill all the way down and across "Nelson" wrote in message ... Hi there, I have 6 columns across going 40 rows down. I'm entering random numbers in row 1 across columns A to F. The numbers have to stay in a range from 1 to 49 but are totally random in row 1. From rows 2 through 40 I want the numbers to automatically fill down consecutively but not to exceed 49. When a certain column hits 49 I want that column to start over at 1 again. I'm a total newbie when it comes to excel. I did try entering A1+1 in cell A2 and then highlighting the remaining rows down to 40 in column A. I then went to the edit menu and choosing fill - down. The problem here was that when I entered any number larger than 10 in row 1 I would go over 49 somewhere down the column. I need the numbers to start back at 1 when 49 is reached. A B C D E F Row 1 46 31 7 15 43 19 Row 2 47 32 8 16 44 20 Row 3 48 33 9 17 45 21 Row 4 49 34 10 18 46 22 Row 5 1 35 11 19 47 23 Row 6 2 36 12 20 48 24 Row 7 3 37 13 21 49 25 Row 8 4 38 14 22 1 26 Row 9 5 39 15 23 2 27 Thanks in Advance Nelson |
#4
|
|||
|
|||
On Mon, 18 Jul 2005 11:19:08 -0700, Nelson wrote:
I have 6 columns across going 40 rows down. I'm entering random numbers in row 1 across columns A to F. The numbers have to stay in a range from 1 to 49 but are totally random in row 1. From rows 2 through 40 I want the numbers to automatically fill down consecutively but not to exceed 49. When a certain column hits 49 I want that column to start over at 1 again. Put this in A2, drag across through F2, and then drag down as far as you need: =IF(A1=49,1,A1+1) This will check to see if the previous number was 49, if this is true, it will put "1", otherwise, it will add 1 to the previous number. MP- -- "Learning is a behavior that results from consequences." B.F. Skinner |
#5
|
|||
|
|||
WOW that was quick!!!
Thanks a bunch guys!!! All 3 of these formulas worked like a charm. Thanks Again Nelson "Mangus Pyke" wrote: On Mon, 18 Jul 2005 11:19:08 -0700, Nelson wrote: I have 6 columns across going 40 rows down. I'm entering random numbers in row 1 across columns A to F. The numbers have to stay in a range from 1 to 49 but are totally random in row 1. From rows 2 through 40 I want the numbers to automatically fill down consecutively but not to exceed 49. When a certain column hits 49 I want that column to start over at 1 again. Put this in A2, drag across through F2, and then drag down as far as you need: =IF(A1=49,1,A1+1) This will check to see if the previous number was 49, if this is true, it will put "1", otherwise, it will add 1 to the previous number. MP- -- "Learning is a behavior that results from consequences." B.F. Skinner |
#6
|
|||
|
|||
On Mon, 18 Jul 2005 12:33:04 -0700, Nelson wrote:
WOW that was quick!!! Thanks a bunch guys!!! All 3 of these formulas worked like a charm. Mine was definitely better than those others.. :) MP- -- "Learning is a behavior that results from consequences." B.F. Skinner |
#7
|
|||
|
|||
Nelson wrote...
I have 6 columns across going 40 rows down. I'm entering random numbers in row 1 across columns A to F. The numbers have to stay in a range from 1 to 49 but are totally random in row 1. From rows 2 through 40 I want the numbers to automatically fill down consecutively but not to exceed 49. When a certain column hits 49 I want that column to start over at 1 again. .... One more option. With a random integer from 1 to 49 in A1, use the following formula in A2. =MOD(A1,49)+1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column filtering to match identical numbers | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
add only positive numbers and avg in a column | Excel Worksheet Functions | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |