Formula for the calculation needed to come up with a total

[Phillse]

I need to figure out out of 10 possible numbers which ones of those 10 equal
a specific number.

An example is:

My total is 50.

I have numbers in cells like this
1, 12, 30, 24, 35, 16, 47, 10, 20

I need to know what this formula of those numbers would be to come up with
the total of 50. The answer would be 30 + 20 = 50

If there a formula that will take that range of number and tell me which
were needed to come up with the total?

Thanks

 

[JoeU2004]

I need to figure out out of 10 possible numbers which ones
of those 10 equal a specific number.

But your example has 9 numbers ;-).

An example is:
[....]
I need to know what this formula of those numbers would be
to come up with the total of 50.
The answer would be 30 + 20 = 50

So a more precise statement of your problem is: you need know which
combination of 2 or more numbers (1 or more?) sums to the target value.
What if there is more than one combination?

If there a formula that will take that range of number and tell
me which were needed to come up with the total?

I would be surprised if there is such a formula, even for finding just one
combination, other than a formula that invokes a UDF written in VBA.

But I can offer a formula that tells you how many combinations you must try,
if you want to find all combinations of 1 or more numbers.

=sumproduct(combin(10,row(1:10))

Interesting: this is equal to =2^10-1, and more generally =2^n-1.


----- original message -----

 

[joel]

I wrote something very similar to this request earlier today. Try this
code. there are two answers. You can put as many numbers into the array
below an input box will ask you what total you want.

The answers are
20 & 30
24,16,10


Sub FindTotal()

Dim Combo()

Data = Array(1, 12, 30, 24, 35, 16, 47, 10, 20)

DataLen = UBound(Data) + 1

Total = Val(InputBox("Enter Total wanted : "))

ReDim Combo(DataLen)
Level = 1
RowCount = 1
ActiveSheet.Cells.ClearContents
Call Recursive(Data, Combo(), Level, Total, RowCount)

End Sub
Sub Recursive(Data, Combo, Level, Total, RowCount)

DataLen = UBound(Data) + 1
'make combination
For Count = (Combo(Level - 1) + 1) To _
DataLen

Combo(Level) = Count
MyTotal = 0
For ColCount = 1 To Level
MyTotal = MyTotal + Data(Combo(ColCount) - 1)
Next ColCount
If MyTotal = Total Then

For ColCount = 1 To Level
Cells(RowCount, ColCount) = _
Data(Combo(ColCount) - 1)
Next ColCount
Cells(RowCount, DataLen + 1) = MyTotal
RowCount = RowCount + 1
End If

If Level < DataLen Then
Call Recursive(Data, Combo, Level + 1, Total, RowCount)
End If
Next Count
End Sub

 

[JoeU2004]

But I can offer a formula that tells you how many combinations you must
try, if you want to find all combinations of 1 or more numbers.

=sumproduct(combin(10,row(1:10))

Interesting: this is equal to =2^10-1, and more generally =2^n-1.

Well, that shoulda been obvious. Wasn't thinking.

PS: Both formulas assume that there are no duplicate numbers among the 10
(or n), or you want to treat each duplicate number as distinct. For
example, suppose your numbers are 10, 15, 25 and 25, and your target is 50.
You would want 25+25 as a solution. But do you really want two solutions
that are 10+15+25 and 10+15+25?


----- original message -----

I need to figure out out of 10 possible numbers which ones
of those 10 equal a specific number.

But your example has 9 numbers ;-).

An example is:
[....]
I need to know what this formula of those numbers would be
to come up with the total of 50.
The answer would be 30 + 20 = 50

So a more precise statement of your problem is: you need know which
combination of 2 or more numbers (1 or more?) sums to the target value.
What if there is more than one combination?

If there a formula that will take that range of number and tell
me which were needed to come up with the total?

I would be surprised if there is such a formula, even for finding just one
combination, other than a formula that invokes a UDF written in VBA.

But I can offer a formula that tells you how many combinations you must
try, if you want to find all combinations of 1 or more numbers.

=sumproduct(combin(10,row(1:10))

Interesting: this is equal to =2^10-1, and more generally =2^n-1.


----- original message -----

I need to figure out out of 10 possible numbers which ones of those 10
equal
a specific number.

An example is:

My total is 50.

I have numbers in cells like this
1, 12, 30, 24, 35, 16, 47, 10, 20

I need to know what this formula of those numbers would be to come up
with
the total of 50. The answer would be 30 + 20 = 50

If there a formula that will take that range of number and tell me which
were needed to come up with the total?

Thanks

 

[Phillse]

Well, that shoulda been obvious. Wasn't thinking.

PS: Both formulas assume that there are no duplicate numbers among the 10
(or n), or you want to treat each duplicate number as distinct. For
example, suppose your numbers are 10, 15, 25 and 25, and your target is 50.
You would want 25+25 as a solution. But do you really want two solutions
that are 10+15+25 and 10+15+25?

No the formula can only use the numbers that are there and those only once.
It can't figure it can take 25 twice. If 25 is only entered once that is all
it can use. I would want all possible varibles as long as those numbers are
already entered.

----- original message -----

I need to figure out out of 10 possible numbers which ones
of those 10 equal a specific number.

But your example has 9 numbers ;-).

An example is:
[....]
I need to know what this formula of those numbers would be
to come up with the total of 50.
The answer would be 30 + 20 = 50

So a more precise statement of your problem is: you need know which
combination of 2 or more numbers (1 or more?) sums to the target value.
What if there is more than one combination?

If there a formula that will take that range of number and tell
me which were needed to come up with the total?

I would be surprised if there is such a formula, even for finding just one
combination, other than a formula that invokes a UDF written in VBA.

But I can offer a formula that tells you how many combinations you must
try, if you want to find all combinations of 1 or more numbers.

=sumproduct(combin(10,row(1:10))

Interesting: this is equal to =2^10-1, and more generally =2^n-1.


----- original message -----

I need to figure out out of 10 possible numbers which ones of those 10
equal
a specific number.

An example is:

My total is 50.

I have numbers in cells like this
1, 12, 30, 24, 35, 16, 47, 10, 20

I need to know what this formula of those numbers would be to come up
with
the total of 50. The answer would be 30 + 20 = 50

If there a formula that will take that range of number and tell me which
were needed to come up with the total?

Thanks

 

[JoeU2004]

If 25 is only entered once that is all it can use.

I never said anything different. At issue is whether you want to exclude
"duplicate" combinations caused by the fact that some numbers appear
multiple times in the list. You seem to indicate you don't; but I still
wonder, since that seems unusual.

The following is one solution. I took the liberty of including a duplicate
24 to make a list of 10, per your original specifications. It demonstrates
the problem I'm anticipating. It is not difficult to avoid duplication;
just additional overhead.

This implementation works for a list of up to 31 numbers. That seems
sufficient since any larger list would require looking at more than
4,294,967,295 combinations. On my computer, that would take about 9.5
hours.

The output goes to the VBA Immediate Window; type ctrl-G to see it. It
would not be difficult to output to cells in a worksheet instead. It also
would not be difficult to use cells in a worksheet so specify the input
parameters, i.e. the list of numbers and the target sum.


Sub doit()
'show combinations of num that sum to target
Dim num, soln
Dim n As Integer, i As Integer, s As Integer
Dim sel As Long, xsel As Long
Dim total As Double

Const target As Double = 50
num = Array(1, 12, 30, 24, 35, 16, 47, 10, 20, 24)

n = UBound(num)
ReDim soln(n) 'solution; subset of num()
sel = 2 ^ (n + 1) - 1 'selector
Debug.Print "------"

Do
total = 0: i = 0: s = -1
xsel = sel
Do
If xsel And 1 Then
total = total + num(i)
s = s + 1: soln(s) = num(i)
End If
xsel = xsel \ 2: i = i + 1
Loop Until xsel = 0

If total = target Then
'use ctrl-G to see solutions
For i = 0 To s
Debug.Print soln(i);
Next i
Debug.Print
End If

sel = sel - 1
Loop Until sel = 0
End Sub


Theory of operation. Each bit in "sel" represents a member of the list
"num". Since selection of a member is binary (select it or not), we treat
"sel" as a binary number. Subtracting one from "sel" each iteration cycles
through all binary combinations.


----- original message -----

Well, that shoulda been obvious. Wasn't thinking.

PS: Both formulas assume that there are no duplicate numbers among the
10
(or n), or you want to treat each duplicate number as distinct. For
example, suppose your numbers are 10, 15, 25 and 25, and your target is
50.
You would want 25+25 as a solution. But do you really want two solutions
that are 10+15+25 and 10+15+25?

No the formula can only use the numbers that are there and those only
once.
It can't figure it can take 25 twice. If 25 is only entered once that is
all
it can use. I would want all possible varibles as long as those numbers
are
already entered.

----- original message -----

I need to figure out out of 10 possible numbers which ones
of those 10 equal a specific number.

But your example has 9 numbers ;-).


An example is:
[....]
I need to know what this formula of those numbers would be
to come up with the total of 50.
The answer would be 30 + 20 = 50

So a more precise statement of your problem is: you need know which
combination of 2 or more numbers (1 or more?) sums to the target value.
What if there is more than one combination?


If there a formula that will take that range of number and tell
me which were needed to come up with the total?

I would be surprised if there is such a formula, even for finding just
one
combination, other than a formula that invokes a UDF written in VBA.

But I can offer a formula that tells you how many combinations you must
try, if you want to find all combinations of 1 or more numbers.

=sumproduct(combin(10,row(1:10))

Interesting: this is equal to =2^10-1, and more generally =2^n-1.


----- original message -----

I need to figure out out of 10 possible numbers which ones of those 10
equal
a specific number.

An example is:

My total is 50.

I have numbers in cells like this
1, 12, 30, 24, 35, 16, 47, 10, 20

I need to know what this formula of those numbers would be to come up
with
the total of 50. The answer would be 30 + 20 = 50

If there a formula that will take that range of number and tell me
which
were needed to come up with the total?

Thanks