Increment a number by multiple criteria Excel 2007

M

Mark Watlock

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
 
G

Gary''s Student

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
 
R

Rick Rothstein

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.
 
G

Glenn

Mark said:
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
Click to expand...

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)
 
G

Glenn

Glenn said:
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)
Click to expand...


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)
 
R

Rick Rothstein

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.
 
Top