Consecutive Numbers down a column not to Exceed 49

Nelson,

Instead of
=A1+1

Use

=IF(A1+1=50,1,A1+1)

HTH,
Bernie
MS Excel MVP


"Nelson" <Nelson@discussions.microsoft.com> wrote in message
news:1F13A1D3-71D9-41CA-B304-DFB8D907AB48@microsoft.com...
> 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
>

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

On cell A2 type
=A1*(A1<49)+1
and fill all the way down and across


"Nelson" <Nelson@discussions.microsoft.com> wrote in message
news:1F13A1D3-71D9-41CA-B304-DFB8D907AB48@microsoft.com...
> 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
>

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
>

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

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

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

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

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
>

On cell A2 type
=A1*(A1<49)+1
and fill all the way down and across


"Nelson" <Nelson@discussions.microsoft.com> wrote in message
news:1F13A1D3-71D9-41CA-B304-DFB8D907AB48@microsoft.com...
> 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
>

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

Nelson,

Instead of
=A1+1

Use

=IF(A1+1=50,1,A1+1)

HTH,
Bernie
MS Excel MVP


"Nelson" <Nelson@discussions.microsoft.com> wrote in message
news:1F13A1D3-71D9-41CA-B304-DFB8D907AB48@microsoft.com...
> 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
>

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

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

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
>

On cell A2 type
=A1*(A1<49)+1
and fill all the way down and across


"Nelson" <Nelson@discussions.microsoft.com> wrote in message
news:1F13A1D3-71D9-41CA-B304-DFB8D907AB48@microsoft.com...
> 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
>

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

Nelson,

Instead of
=A1+1

Use

=IF(A1+1=50,1,A1+1)

HTH,
Bernie
MS Excel MVP


"Nelson" <Nelson@discussions.microsoft.com> wrote in message
news:1F13A1D3-71D9-41CA-B304-DFB8D907AB48@microsoft.com...
> 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
>

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