Increment a number by multiple criteria Excel 2007

  • Thread starter Thread starter Mark Watlock
  • Start date Start date
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
 
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
 
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.
 
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)
 
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)
 
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.
 
Back
Top