Increment a number by multiple criteria Excel 2007
 

I was asked by a co-worker to create a formula to do this but have drawn a
blank. Any ideas?
The next number increases by 11 until last digit is 0 then increases by 10
then 11 again
2619
2630
2640
2651
2662
2673
2684
2695
2706

Increment a number by multiple criteria Excel 2007
 

In A2:
=IF(RIGHT(A1,1)<"0",A1+11,A1+10)
In A3:
=IF(RIGHT(A2,1)<"0",A2+11,IF( RIGHT(A1,1)="0",A2+11,A2+10))
and copy A3 down. We see:

2619
2630
2640
2651
2662
2673
2684
2695
2706
2717
2728
2739
2750
2760
2771
2782
2793
2804
2815
2826
2837
2848
2859
2870
2880

--
Gary''s Student - gsnu200817


"Mark Watlock" wrote:

I was asked by a co-worker to create a formula to do this but have drawn a
blank. Any ideas?
The next number increases by 11 until last digit is 0 then increases by 10
then 11 again
2619
2630
2640
2651
2662
2673
2684
2695
2706

Increment a number by multiple criteria Excel 2007
 

If you fill in the first two rows, then you can use this formula for the remaining rows...

=IF(RIGHT(A2)="0",IF(RIGHT(A1)="0",A2+11,A2+10),A2 +11)

So, you would have to fill in A1=2619 and A2=630, then put the above formula in A3 and copy it down.

--
Rick (MVP - Excel)


"Mark Watlock" wrote in message ...
I was asked by a co-worker to create a formula to do this but have drawn a
blank. Any ideas?
The next number increases by 11 until last digit is 0 then increases by 10
then 11 again
2619
2630
2640
2651
2662
2673
2684
2695
2706

Increment a number by multiple criteria Excel 2007
 

Mark Watlock wrote:
I was asked by a co-worker to create a formula to do this but have drawn a
blank. Any ideas?
The next number increases by 11 until last digit is 0 then increases by 10
then 11 again
2619
2630
2640
2651
2662
2673
2684
2695
2706


A single formula solution. With your starting number in A1, put this in A2 and
copy down:

=A1+11-(MOD(COUNT($A$1:A1)+RIGHT($A$1,1),11)+1=1)

Increment a number by multiple criteria Excel 2007
 

Glenn wrote:
Mark Watlock wrote:
I was asked by a co-worker to create a formula to do this but have
drawn a blank. Any ideas?
The next number increases by 11 until last digit is 0 then increases
by 10 then 11 again
2619 2630
2640
2651
2662
2673
2684
2695
2706


A single formula solution. With your starting number in A1, put this in
A2 and copy down:

=A1+11-(MOD(COUNT($A$1:A1)+RIGHT($A$1,1),11)+1=1)


Not sure how I ended up with "+1=1" at the end of that, but obviously this would
work as well:

=A1+11-(MOD(COUNT($A$1:A1)+RIGHT($A$1,1),11)=0)

Increment a number by multiple criteria Excel 2007
 

Taking a page from your approach, here is an array-entered formula** that can be placed directly in A2 and copied down as well...

=A1+11-(MOD(SUM(--(MOD(A$1:A1,10)=0)),2)=1)

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)


"Glenn" wrote in message ...
Glenn wrote:
Mark Watlock wrote:
I was asked by a co-worker to create a formula to do this but have
drawn a blank. Any ideas?
The next number increases by 11 until last digit is 0 then increases
by 10 then 11 again
2619 2630
2640
2651
2662
2673
2684
2695
2706


A single formula solution. With your starting number in A1, put this in
A2 and copy down:

=A1+11-(MOD(COUNT($A$1:A1)+RIGHT($A$1,1),11)+1=1)


Not sure how I ended up with "+1=1" at the end of that, but obviously this would
work as well:

=A1+11-(MOD(COUNT($A$1:A1)+RIGHT($A$1,1),11)=0)