Dear All,
I have a data series composed of 5 numbers, in 5 different cell in the same column:
07
18
34
35
40
I would like excel to get me all the combinations possible if I add 1, subtract 1 or leave it as it is, in the next columns. Example:
07 08 06 07
18 18 18 19
34 34 34 34
35 35 35 35
40 40 40 40 etc.
Is that in any way possible? Do you have any idea how to implement this?
I use Excel 2007.
Many thanks,
Is this what you had in mind?
Public Sub Options()
Dim Number As String
Range("A1").Select
Do Until ActiveCell = ""
Number = ActiveCell
ActiveCell.Offset(0, 1) = Number - 1
ActiveCell.Offset(0, 2) = Number + 1
ActiveCell.Offset(0, 3) = Number
ActiveCell.Offset(1, 0).Select
Loop
End Sub
hello!
thank you very much for your reply!
this is what i have in mind, but it does not give all the probabilities, i believe there should be at least 125 columns! is VB is the only way?
many thanks,
Put your initial data in row A1 to A5
Enter below formula in B1 and copy it into B1 through II5. There are 3^5 = 243 combinations/columns, so with the maximum of 256 columns it is a narrow escape!
NSV
Please Login or Register to view this content.
Well I had to edit this one. Someone has a tag stating "Remember to wrap code" and I did so obediently, but it makes it impossible to copy.
Here it follows in plain writing:
=IF(ROUNDDOWN(3*((COLUMN()-1)/(3^ROW())-INT((COLUMN()-1)/(3^ROW()))),0)=0,$A1,IF(ROUNDDOWN(3*((COLUMN()-1)/(3^ROW())-INT((COLUMN()-1)/(3^ROW()))),0)=2,$A1+1,$A1-1))
I cannot re-check it, as my Excel is not in english so I must go via VBA to get the english version
Last edited by nsv; 10-24-2007 at 08:06 AM.
thanks you so much!
am examining your formula.
you are a genius!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks