How to calculate lottery combinations by sums part 2

[shadowfighter666]

Hello everyone:

How to calculate lottery combinations by sums

Hello, Is there any way to calculate sum combinations? Simple for example 6/49 lottery total combinations 13.983.816 Using all 49 numbers…. Minimum sum 1+2+3+4+5+6 = 21 (1 combination) Maximum sum 44+45+46+47+48+49= 279 (1 combination) Question-1 how much combinations will be find with each...

www.mrexcel.com

The link above has a working formula for me to use for VBA, but I cannot use it for 80 lottery numbers that draw 20 numbers per draw. It errors out to an Overflow. Please assist, many thanks!

I believe it has to do with the variables declare as Long, but I am not sure how to change it to make it work.

 

[jasonb75]

I think that you will find that such a large pool will be beyond the capabilities of excel, and most likely any computer that had less than 9 digits on the price tag.

The code was written for a draw of 6 from 49, which is 13,983,816 possible combinations.

For your requirement there would be circa 3,535,316,142,212,180,000 possible combinations, that is a ludicrous amount of data to try and process.

 

[shadowfighter666]

I know, but if I don't have a way of doing it manually. Thanks for trying.

 

[shg]

There's no need to ratchet through all the combinations and sum the results to calculate the histogram of sums; it can be calculated in closed form with a generating function (see thread). However, a Double data type runs out of resolution at 2^53 (about 9,007,199,254,740,990). A Double can store much larger numbers, of course, but not with a resolution of 1.

The number of ways to get to the sum 714 with 20 numbers selected from the set 1 to 80 is 8,933,096,352,127,630, right up at the edge. Beyond that, adding loses precision. The peak is the sum 810, for which there are (about) 15,542,763,534,960,600 ways to arrive.

I reckon the function could be modified to use Decimal data types, but it would be way, way slower than the 10s or so it takes to calculate combinations of 80, or the eye blink it takes for combinations of 49.

 

[joeu2004]

Double data type runs out of resolution at 2^53 (about 19,007,199,254,740,990)

Minor typos: 2^53 is 9,007,199,254,740,992.

Excel actually calculates 2^53 exactly, but it only formats up to 15 significant digits (rounded). That is why we see 9,007,199,254,740,990.

(Note that =SUM(2^53,-(2^53&"")) returns 2.)

 

[shadowfighter666]

Thanks all I will give a shot