| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count the number of cells that meet multiple criteria? | Excel Worksheet Functions | |||
| Auto Increment Number | Excel Discussion (Misc queries) | |||
| excel 2007 multiple sort criteria | Excel Discussion (Misc queries) | |||
| Increment Page Numbers for multiple tabs in Excel | Excel Worksheet Functions | |||
| How to auto-number (increment) invoices in Excel? | Excel Discussion (Misc queries) | |||
Hybrid Mode
