combinations in excel

[michaelsmith559]

How would I calculate and list in excel the number of combinations that equal a given average? For example, how many ways can each of these values be used together to equal $75?
Values: $25, $30, $40, $50, $75, $100.
So obviously, $50 & $100 used 1 time each average $75.
How can I show how many combinations in total equal $75 & how can I list how many times the values are used together to equal that average? Thanks.

 

[DRSteele]

Let's start here to see if we get anywhere.

[Please amend your profile signature so that it includes the version of excel you're using.]

Book1
	D	E	F	G	H	I	J
2	75
3
4	avg	20	30	40	50	75	100
5	20	20.0	25.0	30.0	35.0	47.5	60.0
6	30	25.0	30.0	35.0	40.0	52.5	65.0
7	40	30.0	35.0	40.0	45.0	57.5	70.0
8	50	35.0	40.0	45.0	50.0	62.5	75.0
9	75	47.5	52.5	57.5	62.5	75.0	87.5
10	100	60.0	65.0	70.0	75.0	87.5	100.0
Sheet1

Cell Formulas
Range	Formula
E5:J10	E5	=AVERAGE($D5,E$4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:J10	Cell Value	=$D$2	text	NO

 

[montecarlo2012]

Sub CountCombinations()

    Dim values() As Variant
    values = Array(25, 30, 40, 50, 75, 100)
    
    Dim target As Long
    target = 75
    
    Dim combos() As Variant
    ReDim combos(0 To 5, 0 To 2)
    combos(0, 0) = 25
    combos(1, 0) = 30
    combos(2, 0) = 40
    combos(3, 0) = 50
    combos(4, 0) = 75
    combos(5, 0) = 100
    
    Dim numCombos As Long
    numCombos = 0
    
    For i = 0 To 5
        For j = i To 5
            For k = j To 5
                For l = k To 5
                    For m = l To 5
                        For n = m To 5
                            If combos(i, 0) + combos(j, 0) + combos(k, 0) + combos(l, 0) + combos(m, 0) + combos(n, 0) = target Then
                                numCombos = numCombos + 1
                                combos(i, 1) = combos(i, 1) + 1
                                combos(j, 1) = combos(j, 1) + 1
                                combos(k, 1) = combos(k, 1) + 1
                                combos(l, 1) = combos(l, 1) + 1
                                combos(m, 1) = combos(m, 1) + 1
                                combos(n, 1) = combos(n, 1) + 1
                            End If
                        Next n
                    Next m
                Next l
            Next k
        Next j
    Next i
    
    Dim output() As Variant
    ReDim output(0 To 6, 0 To 1)
    output(0, 0) = "Value"
    output(0, 1) = "Count"
    
    For i = 0 To 5
        output(i + 1, 0) = combos(i, 0)
        output(i + 1, 1) = combos(i, 1)
    Next
    
    output(7, 0) = "Total Combinations"
    output(7, 1) = numCombos
    
    Range("A1:B8") = output
    
End Sub

This code uses nested loops to iterate through all possible combinations of the given values and checks whether each combination adds up to the target value. If a combination is found, the code increments the count of each value used in that combination.

The code then outputs the results to a new worksheet, with each row showing a value and the count of how many times it was used in combinations that add up to the target value. The last row shows the total number of combinations that add up to the target value.

To use this code, simply copy and paste it into a new module in your Excel workbook and run the CountCombinations subroutine. The results will be output to a new worksheet in the same workbook.

 

[michaelsmith559]

Sub CountCombinations()

    Dim values() As Variant
    values = Array(25, 30, 40, 50, 75, 100)
   
    Dim target As Long
    target = 75
   
    Dim combos() As Variant
    ReDim combos(0 To 5, 0 To 2)
    combos(0, 0) = 25
    combos(1, 0) = 30
    combos(2, 0) = 40
    combos(3, 0) = 50
    combos(4, 0) = 75
    combos(5, 0) = 100
   
    Dim numCombos As Long
    numCombos = 0
   
    For i = 0 To 5
        For j = i To 5
            For k = j To 5
                For l = k To 5
                    For m = l To 5
                        For n = m To 5
                            If combos(i, 0) + combos(j, 0) + combos(k, 0) + combos(l, 0) + combos(m, 0) + combos(n, 0) = target Then
                                numCombos = numCombos + 1
                                combos(i, 1) = combos(i, 1) + 1
                                combos(j, 1) = combos(j, 1) + 1
                                combos(k, 1) = combos(k, 1) + 1
                                combos(l, 1) = combos(l, 1) + 1
                                combos(m, 1) = combos(m, 1) + 1
                                combos(n, 1) = combos(n, 1) + 1
                            End If
                        Next n
                    Next m
                Next l
            Next k
        Next j
    Next i
   
    Dim output() As Variant
    ReDim output(0 To 6, 0 To 1)
    output(0, 0) = "Value"
    output(0, 1) = "Count"
   
    For i = 0 To 5
        output(i + 1, 0) = combos(i, 0)
        output(i + 1, 1) = combos(i, 1)
    Next
   
    output(7, 0) = "Total Combinations"
    output(7, 1) = numCombos
   
    Range("A1:B8") = output
   
End Sub

This code uses nested loops to iterate through all possible combinations of the given values and checks whether each combination adds up to the target value. If a combination is found, the code increments the count of each value used in that combination.

The code then outputs the results to a new worksheet, with each row showing a value and the count of how many times it was used in combinations that add up to the target value. The last row shows the total number of combinations that add up to the target value.

To use this code, simply copy and paste it into a new module in your Excel workbook and run the CountCombinations subroutine. The results will be output to a new worksheet in the same workbook.

Tried running the code but it gives subscript out of range and highlights this in the code:
output(7, 0) = "Total Combinations"

 

[michaelsmith559]

Let's start here to see if we get anywhere.

[Please amend your profile signature so that it includes the version of excel you're using.]

Book1
	D	E	F	G	H	I	J
2	75
3
4	avg	20	30	40	50	75	100
5	20	20.0	25.0	30.0	35.0	47.5	60.0
6	30	25.0	30.0	35.0	40.0	52.5	65.0
7	40	30.0	35.0	40.0	45.0	57.5	70.0
8	50	35.0	40.0	45.0	50.0	62.5	75.0
9	75	47.5	52.5	57.5	62.5	75.0	87.5
10	100	60.0	65.0	70.0	75.0	87.5	100.0
Sheet1

Cell Formulas
Range	Formula
E5:J10	E5	=AVERAGE($D5,E$4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:J10	Cell Value	=$D$2	text	NO

Dr Steele,
I thought about doing the same thing you did, kind of like rolling two six-sided dice and seeing what the values add up to. However, what if 20+30+100 occurs? I'm not sure how to account combinations like this. Anything else you can think of?

 

[michaelsmith559]

Dr Steele,
I thought about doing the same thing you did, kind of like rolling two six-sided dice and seeing what the values add up to. However, what if 20+30+100 occurs? I'm not sure how to account combinations like this. Anything else you can think of?

Or does the way you did it account for all combinations that would come to that average?

 

[michaelsmith559]

Or does the way you did it account for all combinations that would come to that average?

Let me ask this a different way.
If 25 can occur 6548 times, 30 occur 2856 times, 40 occur 3045 times, 50 occur 2938 times, 75 occur 569 times, and 100 occur 606 times; how many ways can these values combine to average out to 75?

 

[Georgiboy]

Is this the kind of result you are expecting:

Book1
	A	B	C
1	25		75 Average: 75
2	30		50-100 Average: 75
3	40		50-75-100 Average: 75
4	50
5	75
6	100
Sheet1

 

[jdellasala]

This may be useless to you if you're working with Excel 2019 or older, but if not, this might help.

Shitty.xlsx
	A	B	C	D	E	F	G	H	I
1	# of Items:	20		Item	Count		Increment:	5
2	Max #:	100		Count between 0 and 4:	0			0	4
3	28			Count between 5 and 9:	0			5	9
4	68			Count between 10 and 14:	1			10	14
5	17			Count between 15 and 19:	3			15	19
6	40			Count between 20 and 24:	2			20	24
7	85			Count between 25 and 29:	3			25	29
8	34			Count between 30 and 34:	1			30	34
9	19			Count between 35 and 39:	0			35	39
10	45			Count between 40 and 44:	1			40	44
11	22			Count between 45 and 49:	1			45	49
12	17			Count between 50 and 54:	1			50	54
13	26			Count between 55 and 59:	1			55	59
14	64			Count between 60 and 64:	1			60	64
15	54			Count between 65 and 69:	2			65	69
16	24			Count between 70 and 74:	1			70	74
17	26			Count between 75 and 79:	0			75	79
18	85			Count between 80 and 84:	0			80	84
19	57			Count between 85 and 89:	2			85	89
20	70			Count between 90 and 94:	0			90	94
21	11			Count between 95 and 99:	0			95	99
22	65
Sheet1

Cell Formulas
Range	Formula
D2:D21	D2	="Count between "&H2#&" and "&I2#&": "
E2:E21	E2	=COUNTIFS(A3#,">="&H2#,A3#,"<="&I2#)
H2:H21	H2	=SEQUENCE(B2/H1,,0,H1)
I2:I21	I2	=H2#+$H$1-1
A3:A22	A3	=RANDARRAY(B1,,0,B2,1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H1	Whole number	between 2 and B1

 

[DRSteele]

Dr Steele,
I thought about doing the same thing you did, kind of like rolling two six-sided dice and seeing what the values add up to. However, what if 20+30+100 occurs? I'm not sure how to account combinations like this. Anything else you can think of?

I misunderstood the problem you presented. Sorry 'bout dat.