Excel help need Combination FROM Lexicographix number

Reply #26

RL-RANDOMLOGIC — Wed, 15 Sep 2010 12:24:47 GMT

If anyone is interested in getting the set from the lexigraph number, Here is a way to

calculate it using a calculator.

first calculate the highest lexi for the matrix example 5-39

39C5 = 575757

NOW SAY YOUR LOOKING FOR THE SET FROM LEXI VALUE 120,089

SUBTRACT 575757 - 120089 = 455668

NOW FIND THE FIRST VALUE THAT IS LESS THEN OR EQUAL TO 455668

39C5 = 575757

38C5 = 501942

37C5 = 435897

THIS VALUE IS LESS THEN OR EQUAL TO 455668 SO 39-37 = 2, SO A1 =2... [ More ]

Reply #25

JADELottery — Wed, 08 Sep 2010 16:13:39 GMT

Nifty page, bookmarked that one.

Thanks.

Reply #24

stoopendaal — Wed, 08 Sep 2010 11:12:26 GMT

Hi,

For everybody interested in the original VB code it can be found here: http://www.xtremevbtalk.com/archive/index.php/t-168296.html

I converted the code to vb.net and use it for my own projects.

Cheers

Reply #23

kenya649 — Wed, 08 Sep 2010 06:49:50 GMT

very impressed by your work

keep it up

Thanks

Reply #22

JADELottery — Tue, 07 Sep 2010 14:16:31 GMT

Ok, this should be that last update for this file.

Below the links, we are also posting the Excel - Visual Basic Code and the Visual Studio - Visual Basic Code.

Each has been formatted for their respective platforms.

You can download the Excel file at the following:

File - ftp://www.jadexcode.com/excel/combinatorialindex.xls

or

Folder - ftp://www.jadexcode.com/excel

Excel - Visual Basic Code

Function RandomLowerUpper(ByVal L As Double, ByVal U As Double) As

Reply #21

JADELottery — Tue, 07 Sep 2010 13:46:32 GMT

If it's faster in your tests, use it.

We did the code this way because the application.combin() is not available in a programming platform like Visual Studio - Visual Basic.

You can for the most part, copy and paste the code directly into VS-VB and it should work.

The exception being the Combin2Index() function; there you would have to transform the ' theRange As Range' into an array ' theRange() As Integer '.

Reply #20

kenya649 — Tue, 07 Sep 2010 08:24:41 GMT

Hi JADE / WINSUMLOOSESUM,

I have looked at you codes and I must appreciate the great work and thinking involved.

I was wondering why you have to create comb function which calls perm and fact functions instead of using Excel inbuilt combin function i.e application.combin(Z,R)

When dealing with large array which is faster between your function (comb(Z,R)) and Excel inbuilt function (Application.Combin(Z,R))?

Thanks and keep the good work

Kenya649

Reply #19

JADELottery — Tue, 07 Sep 2010 06:33:25 GMT

There have been some changes, but it looks like if you download with Internet Explorer the file may not reload correctly.

You might have to clear out the IE Browser Cache or Use Mozilla Firefox, it downloads the updated file correctly each time.

It's another one of those Microsoft things.

Reply #18

JADELottery — Mon, 06 Sep 2010 20:40:44 GMT

We've added some extra stuff and examples in the ' Function Information ' sheet (bottom tab).

You can download the Excel file at the following:

File - ftp://www.jadexcode.com/excel/combinatorialindex.xls

or

Folder - ftp://www.jadexcode.com/excel

Reply #17

JADELottery — Mon, 06 Sep 2010 17:15:10 GMT

Those of you with 64-bit machines may have a better chance of handling larger Pool and Pick sizes.

It also may be dependent on the Excel version or Language platform.

For the most part, if you keep the values to a Pool Size of (1 - 99) and Pick Size (1 - 10), you should do ok.

Reply #16

JADELottery — Mon, 06 Sep 2010 16:39:32 GMT

Ok, it's out there.

There are also a few tweaks in the workbook itself.

Enjoy.

You can download the Excel file at the following:

File - ftp://www.jadexcode.com/excel/combinatorialindex.xls

or

Folder - ftp://www.jadexcode.com/excel

If you would like just the code, it's posted below.

The VB Macro Source Code

Function Fact(N As Integer) As Double

If (N = 1) Then

Fact = 1

Else

Fact = N * Fact(N - 1)

End If

End Function

Function Perm(N... [ More ]

Reply #15

JADELottery — Mon, 06 Sep 2010 15:29:05 GMT

We're in the process of making one more tweak.

It has to do with the String that is returned and the value of N going in.

It's more of a formatting issue.

When the value of N is greater than 99, the String will display something like, (... 89 90 99 106 ...)

We are reformatting it to display with leading 0's like this (... 089 090 099 106 ...)

This will make it easier when Separating the Combination in to individual values.

So if you want to wait a bit, we'll have it out later.... [ More ]

Reply #14

JADELottery — Mon, 06 Sep 2010 15:13:25 GMT

We've made one more tweak to the code.

This should get larger combination sets, but be careful.

We're not sure how well it works with larger sets beyond about Pick 10; maybe less.

You can download the Excel file at the following:

File - ftp://www.jadexcode.com/excel/combinatorialindex.xls

or

Folder - ftp://www.jadexcode.com/excel

If you would like just the code, it's posted below.

The VB Macro Source Code

Function Fact(N As Integer) As Double

If (N = 1) Then

Fact... [ More ]

Reply #13

frenchie — Mon, 06 Sep 2010 05:12:58 GMT

JADE and Winsum,

Thank you so much to both of you, now I've got what I wanted, this is working perfectly for what I do.

Regards.

Reply #12

winsumloosesum — Sun, 05 Sep 2010 15:09:17 GMT

Jade this is excellent. Thanks for the share.

RL also sent me excellent information also.

I always wanted to know how to do this.

This may help.

I updated Jadelottery's Excel file CombinatorialIndex.xls

Here is the download link: http://www.box.net/shared/hxo736heiq

Look at the image below.

Step #1 - I added the highest value or the maximum number of balls drawn. For example I entered a 49 in cell L1

Step #2 - I added the highest value for the total numal of balls... [ More ]

Reply #11

JADELottery — Sun, 05 Sep 2010 14:50:13 GMT

FYI, the Data Types are the Double, Long, Integer, and String parts of the code.

the Excel file is setup and ready to be manipulated as you see fit, however you need to enable macros when using it.

if you don't want to download the file, then copy and paste the posted code here into your VB macro editor.

we recommend looking over the Excel file though, because the file has some functions that can break up the returned string value in the =Index2Combin(N, R, Index) function.

Reply #10

Raven62 — Sun, 05 Sep 2010 13:53:28 GMT

It would be nice if it was as easy as this:

Cell B2...........................Cell C2 thru Cell G2

=CSN(3819816,5,56) = 52 53 54 55 56

Reply #9

JADELottery — Sun, 05 Sep 2010 13:17:10 GMT

i know what you are looking for, but i've not had much time to look into the problem any further.

actually, if you look carefully at the code, you could find a solution to your problem.

what you see is how far we got and we are also looking for the same single function.

i'm sure there is a simple solution, however we like to take the long way around to finding the solution.

we learn a lot more that way.

Reply #8

frenchie — Sun, 05 Sep 2010 05:38:45 GMT

Thank you very much JADE, for your extensive work ,I'm impressed.

But one question : this is the second time I'm asking for a Excel formula, and both time I've got answered with a macro solution, is that mean that it's not posible to get the result with a formula ?

For the time been, I will change my file and use your macro instead.

Thank you JADE... Regards.

Reply #7

JADELottery — Sun, 05 Sep 2010 03:22:47 GMT

Corrected a Data Type glitch, but could have some limitations on very large Combinatorial Sets.

Play around with the Data Types on different programming platforms.

_________________________________________

Function Fact(N As Integer) As Double

If (N 1) Then

Fact = 1

Else

Fact = N * Fact(N - 1)

End If

End Function

Function Perm(N As Integer, R As Integer) As Double

If (N R) Then

Perm = 0

Else

Perm = Fact(N) / Fact(N - R)

End If

End Function

Reply #6

JADELottery — Sun, 05 Sep 2010 01:52:17 GMT

Ok, Here It Is.

You can download the Excel file at ftp://www.jadexcode.com/excel

The file name is CombinatorialIndex.xls

The VB Macro Source Code is shown below

Function Fact(N As Integer) As Double

If (N 1) Then

Fact = 1

Else

Fact = N * Fact(N - 1)

End If

End Function

Function Perm(N As Integer, R As Integer) As Double

If (N R) Then

Perm = 0

Else

Perm = Fact(N) / Fact(N - R)

End If

End Function

Function Comb(N As Integer, R As Integer

Reply #5

frenchie — Sat, 04 Sep 2010 22:46:14 GMT

Hi JADE,

Thank you for your prompt respond, I look forward to hearing from you.

Reply #4

JADELottery — Sat, 04 Sep 2010 20:36:55 GMT

working on it, but we call it a Combinatorial Index.

we have it going from a Combination to an Index.

now we have to set it up for an Index to a Combination.

Reply #3

frenchie — Thu, 02 Sep 2010 23:46:53 GMT

Anyone ? please Thanks.

Reply #2

frenchie — Wed, 01 Sep 2010 06:24:25 GMT

Thank you Raven62,

I know I had it posted before but the answer was with a macro, and not a FORMULA, so if someone can help, it would be highly appreciated, I need a formula or formulas if posible.

Thank you.

Reply #1

Raven62 — Wed, 01 Sep 2010 03:27:18 GMT

https://www.lotterypost.com/thread/217450/1711409

Excel help need Combination FROM Lexicographix number

frenchie — Tue, 31 Aug 2010 20:08:13 GMT

Hi every one,

What formula will be needed to get a combination of 5/56 FROM the lexicographic number ?

Let say I have in cell : A1 the last lexicographic number 3819816.

What formula would be needed to create the combination in cell : C1,D1,E1,F1,G1. ?

THANK YOU ALL