Hi guys, i have a 50 set data with each data has either 2 or 3 results - for now 33 data set has 3 results (high, moderate, low) and 17 has 2 results (high, Low). May I know what formula should i use to calculate all the potential combination? Each of the outcome must have 50 data points (variations).
Thank you!
Do you realise how many combinations that can produce? Just taking your 17 variables which have either High or Low settings, and using a binary number where 0 = Low and 1 = High, the first few in the sequence would be:
00000000000000000, i.e. all of them are Low
00000000000000001, i.e. all are Low except for the right-most
00000000000000010, i.e. all are Low except for the 2nd right-most
00000000000000011, i.e. all are Low except for the two right-most
00000000000000100, i.e. all are Low except for the 3rd right-most
and so on. These 17 variables would produce a sequence of 2 raised to the power of 17, or 128k (where k = 1024)
And that is ignoring the 33 variables which can have 3 states. There are not enough rows in an Excel sheet to show all those variations.
Hope this helps.
Pete
Ah, thanks Pete for that answer.
Is there a calculation to see what is the possible outcome - not seeing the bit by bit variations. \
Thank you in advance!
I have tried myself. This has been one of my favorite questions too. I have never been able to find a way to do that ie find a mathematical way to determine all 136 combinations (2/17) or 5456 combinations (3/33) without having to test all values (3 to 98,304 and 7 to 7,516,192,768)to find them.
I did manage to find a way around the 1,048,576 row limit but the calculation load for the next steps made it very impractical.
I wish you the best and if you ever find that math algorithm please come back an share it.
Dave
The number of such combinations is 3^33 * 2^17: (any of 3 from set1) * (any of 3 from set2) * ... * (any of 2 from set34) * ... * (any of 2 from set50)Originally Posted by hamsterr88
Excel displays 728,637,186,579,566,000,000, limited to 15 significant digits (rounded). The exact value is 728,637,186,579,565,510,656.
Both are approximations because the calculation exceeds the limits of 64-bit binary floating-point, which is how Excel represents numbers internally.
Using type Decimal in VBA, the actual number of combinations is 728,637,186,579,565,510,656.
Obviously, that is beyond the ability of most computers to enumerate in any reasonable amount of time. And it is far beyond the ability of Excel to represent each combination.
-----
PS.... Well, whadaya know: the exact binary value is indeed correct (compare to the type Decimal result), in this case. But that's a coincidence.
It is because 3^33 can be calculated and represented exactly internally because it does not exceed 2^53, although Excel rounds the displayed value because it exceeds 15 significant digits.
And 2^n is simply a binary scaling factor for 3^33, so the product can be calculated exactly internally for any n = 1023 - int(log(x,2)), where x = 3^33 in this case.
But hamsterr88 did say "for now". A binary calculation of that magnitude would not be exact if the second 17 sets of data had more than 2 values each, or if other conditions were not within binary arithmetic limits.
Last edited by joeu2004; 12-12-2019 at 11:42 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks