Permutation in excel
 

OK, I will make it easier to understand, what I want is a code in vba t
make those combinations:

Say we have 7 and we want to make combinations with the numbers 1 - 1
in order, always forwards, not backwards like that:

1 2 3 4 5 6 7
1 2 3 4 5 6 8
1 2 3 4 5 6 9
1 2 3 4 5 6 10
1 2 3 4 5 7 8
1 2 3 4 5 7 9
1 2 3 4 5 7 10
1 2 3 4 5 8 9
1 2 3 4 5 9 10
1 2 3 4 6 7 8
1 2 3 4 6 7 9
1 2 3 4 6 7 10
1 2 3 4 6 8 9
1 2 3 4 6 8 10
1 2 3 4 7 8 9
1 2 3 4 7 8 10
1 2 3 4 7 9 10
1 2 3 4 8 9 10
1 2 3 5 6 7 8
1 2 3 5 6 7 9
1 2 3 5 6 7 10
1 2 3 5 6 8 9
1 2 3 5 6 8 10
1 2 3 5 7 8 9
1 2 3 5 7 8 10
1 2 3 5 7 9 10
1 2 3 5 8 9 10
1 2 3 6 7 8 9
1 2 3 6 7 8 10
1 2 3 6 7 9 10
1 2 3 7 8 9 10
1 2 4 5 6 7 8
1 2 4 5 6 7 9
1 2 4 5 6 7 10
1 2 4 5 6 8 9
1 2 4 5 6 8 10
1 2 4 5 6 9 10
1 2 4 5 7 8 9
1 2 4 5 7 8 10
1 2 4 5 7 9 10
1 2 4 6 7 8 9
1 2 4 6 7 8 10
1 2 4 6 7 9 10
1 2 4 6 8 9 10
1 2 4 7 8 9 10
1 2 5 6 7 8 9
1 2 5 6 7 8 10
1 2 5 6 7 9 10
1 2 5 6 8 9 10
1 2 5 7 8 9 10
1 2 6 7 8 9 10
1 3 4 5 6 7 8
1 3 4 5 6 7 9
1 3 4 5 6 7 10
1 3 4 5 6 8 9
1 3 4 5 6 8 10
1 3 4 5 6 9 10
1 3 4 5 7 8 9
1 3 4 5 7 8 10
1 3 4 5 7 9 10
1 3 4 5 8 9 10
1 4 5 6 7 8 9
1 4 5 6 7 8 10
1 4 5 6 7 9 10
1 4 5 6 8 9 10
1 4 5 7 8 9 10
1 5 6 7 8 9 1

--
Swede
-----------------------------------------------------------------------
Sweden's Profile: http://www.excelforum.com/member.php...fo&userid=2732
View this thread: http://www.excelforum.com/showthread.php?threadid=46825

Permutation in excel
 

i have this formula but can not get it to work.

=AVERAGE(IF(T5:T1000="4",IF(L5:L1000<20,M5:M1000)) )

can you see any reason y this might be,

i will explain what i want to know if it is not clear from the formul
i have come up with already,

i want a formula that gives me an average of M if M has a value of les
then 20 and if T=4.

Please help......

--
David
-----------------------------------------------------------------------
David M's Profile: http://www.excelforum.com/member.php...fo&userid=2655
View this thread: http://www.excelforum.com/showthread.php?threadid=46825

Permutation in excel
 

David,

=AVERAGE(IF((L5:L1000<20)*(T5:T1000=4),M5:M1000))

Enter it using Ctrl-Shift-Enter, since this is an array formula.

HTH,
Bernie
MS Excel MVP


"David M" wrote in message
...

i have this formula but can not get it to work.

=AVERAGE(IF(T5:T1000="4",IF(L5:L1000<20,M5:M1000)) )

can you see any reason y this might be,

i will explain what i want to know if it is not clear from the formula
i have come up with already,

i want a formula that gives me an average of M if M has a value of less
then 20 and if T=4.

Please help.......


--
David M
------------------------------------------------------------------------
David M's Profile: http://www.excelforum.com/member.php...o&userid=26556
View this thread: http://www.excelforum.com/showthread...hreadid=468254

Permutation in excel
 

Sub aa()
Dim i, j, k, l, m, n, o, p, rw
rw = 1
For i = 1 To 4
For j = i + 1 To 5
For k = j + 1 To 6
For l = k + 1 To 7
For m = l + 1 To 8
For n = m + 1 To 9
For o = n + 1 To 10
Cells(rw, 1) = i
Cells(rw, 2) = j
Cells(rw, 3) = k
Cells(rw, 4) = l
Cells(rw, 5) = m
Cells(rw, 6) = n
Cells(rw, 7) = o
rw = rw + 1
Next o
Next n
Next m
Next l
Next k
Next j
Next i
End Sub

perhaps

--
Regards,
Tom Ogilvy



"Sweden" wrote in
message ...

OK, I will make it easier to understand, what I want is a code in vba to
make those combinations:

Say we have 7 and we want to make combinations with the numbers 1 - 10
in order, always forwards, not backwards like that:

1 2 3 4 5 6 7
1 2 3 4 5 6 8
1 2 3 4 5 6 9
1 2 3 4 5 6 10
1 2 3 4 5 7 8
1 2 3 4 5 7 9
1 2 3 4 5 7 10
1 2 3 4 5 8 9
1 2 3 4 5 9 10
1 2 3 4 6 7 8
1 2 3 4 6 7 9
1 2 3 4 6 7 10
1 2 3 4 6 8 9
1 2 3 4 6 8 10
1 2 3 4 7 8 9
1 2 3 4 7 8 10
1 2 3 4 7 9 10
1 2 3 4 8 9 10
1 2 3 5 6 7 8
1 2 3 5 6 7 9
1 2 3 5 6 7 10
1 2 3 5 6 8 9
1 2 3 5 6 8 10
1 2 3 5 7 8 9
1 2 3 5 7 8 10
1 2 3 5 7 9 10
1 2 3 5 8 9 10
1 2 3 6 7 8 9
1 2 3 6 7 8 10
1 2 3 6 7 9 10
1 2 3 7 8 9 10
1 2 4 5 6 7 8
1 2 4 5 6 7 9
1 2 4 5 6 7 10
1 2 4 5 6 8 9
1 2 4 5 6 8 10
1 2 4 5 6 9 10
1 2 4 5 7 8 9
1 2 4 5 7 8 10
1 2 4 5 7 9 10
1 2 4 6 7 8 9
1 2 4 6 7 8 10
1 2 4 6 7 9 10
1 2 4 6 8 9 10
1 2 4 7 8 9 10
1 2 5 6 7 8 9
1 2 5 6 7 8 10
1 2 5 6 7 9 10
1 2 5 6 8 9 10
1 2 5 7 8 9 10
1 2 6 7 8 9 10
1 3 4 5 6 7 8
1 3 4 5 6 7 9
1 3 4 5 6 7 10
1 3 4 5 6 8 9
1 3 4 5 6 8 10
1 3 4 5 6 9 10
1 3 4 5 7 8 9
1 3 4 5 7 8 10
1 3 4 5 7 9 10
1 3 4 5 8 9 10
1 4 5 6 7 8 9
1 4 5 6 7 8 10
1 4 5 6 7 9 10
1 4 5 6 8 9 10
1 4 5 7 8 9 10
1 5 6 7 8 9 10


--
Sweden
------------------------------------------------------------------------
Sweden's Profile:
http://www.excelforum.com/member.php...o&userid=27322
View this thread: http://www.excelforum.com/showthread...hreadid=468254

Permutation in excel
 

Thank you Mr Tom Ogilvy!

That code works but there are two problems:

1.
Excel is limited to 65500 cells, if I try with 7numbers bitween 1 - 3
it stops at
the combination:
1 2 4 19 32 34 35
Is there any solution for that problem to make excel continue o
another side or any other solution?

2.I tried to limit the total number of the combination by making this:

Sub aa()
Dim i, j, k, l, m, n, o, p, rw
rw = 1
For i = 1 To 1
For j = i + 1 To 24
For k = j + 1 To 26
For l = k + 5 To 30
For m = l + 3 To 33
For n = m + 5 To 34
For o = n + 7 To 35
Cells(rw, 1) = i
Cells(rw, 2) = j
Cells(rw, 3) = k
Cells(rw, 4) = l
Cells(rw, 5) = m
Cells(rw, 6) = n
Cells(rw, 7) = o
rw = rw + 1
Next o
Next n
Next m
Next l
Next k
Next j
Next i
End Sub

But something is wrong, it stops at cell 55000 and it´s not finishe
with all combinations.
Do you have some advice or can anyone help continue solving thi
problem

--
Swede
-----------------------------------------------------------------------
Sweden's Profile: http://www.excelforum.com/member.php...fo&userid=2732
View this thread: http://www.excelforum.com/showthread.php?threadid=46825

Permutation in excel
 

i want a count if formula simlar to this
=Count(if(T5:T1000=4,if(L5:L1000"=20")) CSE but it does not work,

i have been told that there is no way of using count if to get the
intersections of two conditions is this true?

if this is true or not could you please help me with a way of finding
the information i need?

Thank you,

David,


--
David M
------------------------------------------------------------------------
David M's Profile: http://www.excelforum.com/member.php...o&userid=26556
View this thread: http://www.excelforum.com/showthread...hreadid=468254

Permutation in excel
 

"David M" wrote:
=Count(if(T5:T1000=4,if(L5:L1000"=20")) CSE

Try instead: =SUMPRODUCT((T5:T1000=4)*(L5:L1000=20))
(normal ENTER, not CSE)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Permutation in excel
 

Thank you that works great.

could you explain to me how sumproduct works? if its not to muc
trouble so i know when i would be able to use it, and how to set i
out?

Thank you for your help

Davi

--
David
-----------------------------------------------------------------------
David M's Profile: http://www.excelforum.com/member.php...fo&userid=2655
View this thread: http://www.excelforum.com/showthread.php?threadid=46825

Permutation in excel
 

Try Bob Phillips' excellent treatment on SUMPRODUCT at his:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Scroll down to "SUMPRODUCT Explained" where you'll find all the details
fully explained. And if you do take the time to look through the entire
white paper, you'd be well on your way to mastering it very quickly ! <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"David M" wrote in
message ...

Thank you that works great.

could you explain to me how sumproduct works? if its not to much
trouble so i know when i would be able to use it, and how to set it
out?

Thank you for your help

David


--
David M
------------------------------------------------------------------------
David M's Profile:
http://www.excelforum.com/member.php...o&userid=26556
View this thread: http://www.excelforum.com/showthread...hreadid=468254