Require change to limit exact sum output combinations

Kishan · Jul 4, 2018

Using Excel2000

Hi,

Till now I was using "pgc01 code" which is amazing, I found code under this link
https://www.mrexcel.com/forum/excel-questions/277924-combination-help-5.html#post1424848

I require modifications code only output Permutation Repetition with a desire sums

For example...
In the cells B6:B10 there are numbers 0,1,2,3,4,5 I need all Permutation Repetition could be find with SUM = 43. Is it possible?
Layout is not important could be the any way depending on the new or modified code

Data example....

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

P

14

*

3

4

2

4

2

4

1

43

2

Combinations

FALSE

*

2

4

3

2

3

2

3

4

5

3

4

43

3

Repetition

TRUE

*

5

2

1

4

3

2

3

2

3

4

3

2

5

43

4

*

5

3

5

3

2

5

4

1

3

4

2

43

5

Set Col B5 Down

0

*

4

2

4

5

4

3

2

3

1

3

4

2

43

6

*

1

*

2

4

2

3

5

4

3

4

3

4

3

1

3

43

7

*

2

*

3

1

4

2

3

4

3

5

1

4

1

43

8

*

3

*

3

2

5

4

1

5

3

4

3

2

3

4

43

9

*

4

*

2

3

5

3

4

5

4

1

4

1

2

4

43

10

*

5

*

5

3

1

4

3

4

2

3

1

4

43

11

*

3

5

1

2

4

3

1

5

3

4

43

12

*

5

0

4

5

3

5

3

2

1

3

5

1

2

4

43

13

*

2

3

5

1

4

2

3

2

5

4

3

4

43

14

*

2

3

4

5

2

3

2

3

5

43

15

*

1

3

1

3

5

1

4

3

4

5

3

4

3

43

16

4

2

4

2

4

2

3

4

2

43

17

1

4

1

3

4

5

2

3

4

3

2

3

43

18

19

Sheet1

Thank you in advance

Regards,
Kishan

shg · Jul 4, 2018

If you just need to know how many there are rather than list them (there are over 2 billion),

	A	B	C
1	Sum	Ways
2	0	1	B2:B72: {=polyExp({1,1,1,1,1,1}, 14)}
3	1	14
4	2	105
5	3	560
6	4	2,380
7	5	8,568
8	6	27,118
9	7	77,324
10	8	202,020
11	9	489,580
12	10	1,110,746
13	11	2,376,192
14	12	4,820,543
15	13	9,316,594
16	14	17,218,995
17	15	30,529,240
18	16	52,063,571
19	17	85,593,522
20	18	135,917,523
21	19	208,814,424
22	20	310,829,610
23	21	448,854,900
24	22	629,486,676
25	23	858,182,052
26	24	1,138,276,503
27	25	1,469,971,230
28	26	1,849,435,329
29	27	2,268,186,480
30	28	2,712,905,781
31	29	3,165,803,382
32	30	3,605,583,723
33	31	4,008,970,980
34	32	4,352,660,949
35	33	4,615,482,690
36	34	4,780,499,451
37	35	4,836,766,584
38	36	4,780,499,451
39	37	4,615,482,690
40	38	4,352,660,949
41	39	4,008,970,980
42	40	3,605,583,723
43	41	3,165,803,382
44	42	2,712,905,781
45	43	2,268,186,480
46	44	1,849,435,329
47	45	1,469,971,230
48	46	1,138,276,503
49	47	858,182,052
50	48	629,486,676
51	49	448,854,900
52	50	310,829,610
53	51	208,814,424
54	52	135,917,523
55	53	85,593,522
56	54	52,063,571
57	55	30,529,240
58	56	17,218,995
59	57	9,316,594
60	58	4,820,543
61	59	2,376,192
62	60	1,110,746
63	61	489,580
64	62	202,020
65	63	77,324
66	64	27,118
67	65	8,568
68	66	2,380
69	67	560
70	68	105
71	69	14
72	70	1

I can post the function if that works for you.

Kishan · Jul 4, 2018

shg said:
If you just need to know how many there are rather than list them (there are over 2 billion),
I can post the function if that works for you.

Hi shg, I am amazed to know there could be 2,268,186,480 combinations with sum 43

I appreciate your hard work. Please can I get the function?

Kind Regards,
Kishan

shg · Jul 4, 2018

Code:

Function PolyExp(Poly As Variant, iExp As Long, Optional iUB As Long = -1) As Variant
  ' shg 2014
  ' UDF wrapper for adPolyExp

  Dim ad()          As Double

  If bMake1D(Poly, ad) Then
    ad = adPolyExp(ad, iExp, iUB)
    PolyExp = WorksheetFunction.Transpose(ad)
  Else
    PolyExp = CVErr(xlErrValue)
  End If
End Function

Function adPolyExp(ad() As Double, iExp As Long, Optional ByVal iUB As Long = -1) As Double()
  ' shg 2014
  ' Returns the exponent of the polynomial

  ' VBA only

  Dim i             As Long
  Dim adO()         As Double

  If iExp < 1 Then Stop

  adO = ad
  For i = 2 To iExp
    adO = adPolyMult(adO, ad, iUB)
  Next i

  adPolyExp = adO
End Function

Function adPolyMult(ad1() As Double, ad2() As Double, Optional ByVal iMAX As Long = -1) As Double()
  ' shg 2014
  ' Returns the product of two polynomials

  Dim adO()         As Double
  Dim iUB1          As Long
  Dim iUB2          As Long
  Dim i             As Long
  Dim j             As Long

  iUB1 = UBound(ad1)
  iUB2 = UBound(ad2)
  
  If iMAX = -1 Then iMAX = iUB1 + iUB2
  If iMAX > iUB1 + iUB2 Then iMAX = iUB1 + iUB2
  
  ReDim adO(0 To iMAX)
  
  For i = 0 To iUB1
    For j = 0 To iUB2
      If i + j > iMAX Then Exit For
      adO(i + j) = adO(i + j) + ad1(i) * ad2(j)
    Next j
  Next i
  adPolyMult = adO
End Function

Function bMake1D(av As Variant, ad() As Double) As Boolean
  ' shg 2014
  ' VBA only

  ' Returns a 1D 0-based array of the values in av, which can be a
  ' column or row vector, a 1D array, or a scalar
  
  ' Reuires NumDim

  Dim rArea         As Range
  Dim cell          As Range
  Dim nOut          As Long
  Dim i             As Long
  Dim iLB1          As Long
  Dim iUB1          As Long
  Dim iLB2          As Long
  Dim iUB2          As Long

  If TypeOf av Is Range Then av = av.Value2

  Select Case NumDim(av)
    Case 0
      Select Case VarType(av)
        Case vbDouble, vbLong, vbInteger, vbSingle, vbByte
          ReDim ad(0 To 0)
          ad(0) = av
          bMake1D = True
      End Select

    Case 1
      iLB1 = LBound(av)
      iUB1 = UBound(av)

      ReDim ad(0 To iUB1 - iLB1)
      For i = iLB1 To iUB1
        If VarType(av(i)) = vbDouble Then
          ad(i - iLB1) = av(i)
        Else
          GoTo Oops
        End If
      Next i
      bMake1D = True

    Case 1.5, 2
      iLB1 = LBound(av, 1)
      iUB1 = UBound(av, 1)
      iLB2 = LBound(av, 2)
      iUB2 = UBound(av, 2)

      If iUB1 <> iUB1 And iUB2 <> iLB2 Then
        GoTo Oops

      ElseIf iLB2 = iUB2 Then
        ' column vector
        ReDim ad(0 To iUB1 - iLB1)

        For i = iLB1 To iUB1
          Select Case VarType(av(i, iLB2))
            Case vbDouble, vbLong, vbInteger, vbSingle, vbByte
              ad(i - iLB1) = av(i, iLB2)
            Case Else
              GoTo Oops
          End Select
        Next i
        bMake1D = True

      Else    ' iLB1 = iUB1
        ' row vector
        ReDim ad(0 To iUB2 - iLB2)

        For i = iLB2 To iUB2
          Select Case VarType(av(iLB1, i))
            Case vbDouble, vbLong, vbInteger, vbSingle, vbByte
              ad(i - iLB2) = av(iLB1, i)
            Case Else
              GoTo Oops
          End Select
        Next i
        bMake1D = True

      End If
  End Select

Oops:
End Function

Function NumDim(av As Variant) As Double
  ' shg 2014
  ' Returns
  '   0 for a scalar or single-cell range
  '   1 for a 1D array
  '   1.5 for a row or column-vector range
  '   n for an n-dimensional array

  Dim i             As Long

  If TypeOf av Is Range Then
    If av.Cells.Count = 1 Then
      NumDim = 0
    ElseIf av.Columns.Count = 1 Or av.Rows.Count = 1 Then
      NumDim = 1.5
    Else
      NumDim = 2
    End If

  ElseIf IsArray(av) Then
    On Error GoTo AlmostDone

    For NumDim = 0 To 6000
      i = LBound(av, NumDim + 1)
    Next NumDim
AlmostDone:
    Err.Clear
    If NumDim = 2 Then
      If LBound(av, 1) = UBound(av, 1) Or _
         LBound(av, 2) = UBound(av, 2) Then NumDim = 1.5
    End If
  End If
End Function

Kishan · Jul 4, 2018

shg said:

Code:

Function PolyExp(Poly As Variant, iExp As Long, Optional iUB As Long = -1) As Variant
  ' shg 2014
  ' UDF wrapper for adPolyExp

  Dim ad()          As Double

     Err.Clear
    If NumDim = 2 Then
      If LBound(av, 1) = UBound(av, 1) Or _
         LBound(av, 2) = UBound(av, 2) Then NumDim = 1.5
    End If
  End If
End Function

Hi shg, I liked your function it is calculating all possible combinations with each of sum through 0 to 70 lovely function 

I will see now what condition can be applied to reduce the number of combinations 

Thank you for posting. I appreciate your help and time 

Kind Regards,
Kishan

shg · Jul 4, 2018

You're welcome.

Kishan · Jul 4, 2018

Hi shg, if I use 14 times any of "0,1,2,3,4,5" once in all the 14 positions as per function we can have 1 combination with sum 0 & 1 combination with sum 70, as shown in the row1 used 0=14 Times & in the row2 used 5=14 Times

Instead using all "0,1,2,3,4,5" once in the row can be use multiple times.
For example: number 0=0 Time, 1=1 Time, 2=2 Times, 3=6 Times, 4=5 Times & 5=0 Time. As shown in the row8

Example data

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

Sum

c1

c2

c3

c4

c5

c6

c7

c8

c9

c10

c11

c12

c13

c14

0

1

2

3

4

5

2

1

0

14

0

3

70

5

0

14

4

5

6

7

Sum

c1

c2

c3

c4

c5

c6

c7

c8

c9

c10

c11

c12

c13

c14

0

1

2

3

4

5

8

43

3

4

2

4

2

4

1

0

1

2

6

5

0

9

43

2

4

3

2

3

2

3

4

5

3

4

0

4

6

3

1

10

43

5

2

1

4

3

2

3

2

3

4

3

2

5

0

1

4

3

2

11

43

5

3

5

3

2

5

4

1

3

4

2

0

3

2

3

12

43

4

2

4

5

4

3

2

3

1

3

4

2

0

1

3

5

4

1

13

43

2

4

2

3

5

4

3

4

3

4

3

1

3

0

1

3

5

4

1

14

43

3

1

4

2

3

4

3

5

1

4

1

0

3

1

4

2

15

43

3

2

5

4

1

5

3

4

3

2

3

4

0

1

4

3

2

16

43

2

3

5

3

4

5

4

1

4

1

2

4

0

3

2

5

2

17

43

5

3

1

4

3

4

2

3

1

4

0

3

1

3

6

1

18

43

3

5

1

2

4

3

1

5

3

4

0

2

5

3

2

19

43

5

0

4

5

3

5

3

2

1

3

5

1

2

4

1

2

3

2

4

20

43

2

3

5

1

4

2

3

2

5

4

3

4

0

1

4

3

2

21

43

2

3

4

5

2

3

2

3

5

0

5

2

22

43

1

3

1

3

5

1

4

3

4

5

3

4

3

0

3

0

6

3

2

23

43

4

2

4

2

4

2

3

4

2

0

4

5

0

24

43

1

4

1

3

4

5

2

3

4

3

2

3

0

2

5

3

2

25

26

Sheet3

If each row has different conditions as specified in cells Q8:V24, will it be possible to calculated by the function how much combinations could be made by using different conditions.

Thank you in advance

Kind Regards,
Kishan

shg · Jul 4, 2018

Each of the 14 numbers can be 0 through 5, so the number of possibilities is 6^14, which is 78,364,164,096, which is what all those numbers add up to.

Kishan · Jul 4, 2018

shg said:
Each of the 14 numbers can be 0 through 5, so the number of possibilities is 6^14, which is 78,364,164,096, which is what all those numbers add up to.

Hi shg, you are correct I agree, but my thought is how much combinations could be made say for example in the row within 14 positions I use as follow.

Number1=3 Times
Number2=1 Times
Number3=3 Times
Number4=6 Times
Number5=1 Times

Total numbers are 14 but some 1 time & other many time (0 is not used in the row) as shown row17, which total sum is 43, how much could be made applying these conditions to numbers

Kind Regards,
Kishan

shg · Jul 4, 2018

=product(combin(14-{0,3,4,7,13}, {3,1,3,6,1}))

Require change to limit exact sum output combinations

Kishan

Well-known Member

Excel Facts

shg

MrExcel MVP

Kishan

Well-known Member

shg

MrExcel MVP

Kishan

Well-known Member

shg

MrExcel MVP

Kishan

Well-known Member

shg

MrExcel MVP

Kishan

Well-known Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Require change to limit exact sum output combinations

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock