Find Cells that add up to Specific Sum
 

I have a list of about 30 numbers in individuals cells in a column that added
up to over 200,000. I need to find the numbers within the 30 numbers that
add up specifically to 109,484.60. What formula can I use?
--
BeachLover

Find Cells that add up to Specific Sum
 

Hi,

This can't be done. You can use Solver to find 1 solution, but there may be
a large number of solutions and Solver only returns 1. And this is not a
function, but an Excel Add-in. If you are interested in exploring this topic
area, start by choosing Tools, Add-ins, and check Solver. Then Google Excel
Solver. There are also sample excel files stored in the Samples folder from
Microsoft demonstrating this sophisticated tool.

Why can't it be done? Lets look at a very simple example. You have the
following

1
2
3
4
5
6
7
8
9

And you want to find exactly which numbers total to 10.
1+9
or
2+8
or
3+7
or
4+6
or
5+4+1
....
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"BeachLover" wrote:

I have a list of about 30 numbers in individuals cells in a column that added
up to over 200,000. I need to find the numbers within the 30 numbers that
add up specifically to 109,484.60. What formula can I use?
--
BeachLover

Find Cells that add up to Specific Sum
 

Thank you for the information. I went to Tools, Add-Ins, and checked Solver
Add-ins and then clicked ok but it said it this add-in and it worked. It is
now on my tool bar. However, I have no idea how to use it. I know you said
to google for examples but I have not had much luck with that. Do you or
anyone else have experience with Solver to help me with my diliemma.


--
BeachLover


"Shane Devenshire" wrote:

Hi,

This can't be done. You can use Solver to find 1 solution, but there may be
a large number of solutions and Solver only returns 1. And this is not a
function, but an Excel Add-in. If you are interested in exploring this topic
area, start by choosing Tools, Add-ins, and check Solver. Then Google Excel
Solver. There are also sample excel files stored in the Samples folder from
Microsoft demonstrating this sophisticated tool.

Why can't it be done? Lets look at a very simple example. You have the
following

1
2
3
4
5
6
7
8
9

And you want to find exactly which numbers total to 10.
1+9
or
2+8
or
3+7
or
4+6
or
5+4+1
...
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"BeachLover" wrote:

I have a list of about 30 numbers in individuals cells in a column that added
up to over 200,000. I need to find the numbers within the 30 numbers that
add up specifically to 109,484.60. What formula can I use?
--
BeachLover

Find Cells that add up to Specific Sum
 

Have a look at Tushar Mehta's site for some help on Solver and code.

http://www.tushar-mehta.com/excel/te...ues/index.html

See the links to google threads also at the bottom of the page.

BUT..........................You should read this from Harlan Grove before
embarking on your project.

http://tinyurl.com/a6xzff



Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 15:50:01 -0800, BeachLover
wrote:

Thank you for the information. I went to Tools, Add-Ins, and checked Solver
Add-ins and then clicked ok but it said it this add-in and it worked. It is
now on my tool bar. However, I have no idea how to use it. I know you said
to google for examples but I have not had much luck with that. Do you or
anyone else have experience with Solver to help me with my diliemma.

Find Cells that add up to Specific Sum
 

BeachLover wrote:
I have a list of about 30 numbers in individuals cells in a column that added
up to over 200,000. I need to find the numbers within the 30 numbers that
add up specifically to 109,484.60. What formula can I use?

No mere formula can accomplish this. If you read up on /combinations/, e.g.,

http://tutors4you.com/permutationcom...ontutorial.htm

You will find the number of combinations of values you need to test is

2^30 - 1 = 1,073,741,824

That's over 1 /billion/ combinations! You need code that can loop
through all the possibilities.

The link posted by Gord Dibben might help. Good Luck!

Find Cells that add up to Specific Sum
 

I read a discussion thread from April 25 of 2008 that seems like a similar
question. They told them to do macro. I tried both macros. The first one
there was an error in it and the second one worked but only if you knew the
numbers and had them in a series. I have copied and pasted the macros below.
I do not know the first thing about macros except how to copy and paste the
macro into the vba screen and run it. If anyone know about macros and can
make the first one work for me....I think that it may work.

Here are the two macros that were on that post.
Enter
1525
in Cell A1

135
10000
500
550
1000
25
in cells B1:B6

Run this code:
Sub TestBldbin()
Dim i As Long
Dim bits As Long
Dim varr As Variant
Dim varr1() As Long
Dim rng As Range
Dim iCol As Long
Dim tot As Long
Dim num As Long
iCol = 0
Set rng = Range(Range("B1"), Range("B1").End(xlDown))
num = 2 ^ rng.Count - 1
bits = rng.Count
varr = rng.Value
ReDim varr1(0 To bits - 1, 0 To 0)
For i = 0 To num
bldbin i, bits, varr1
tot = Application.SumProduct(varr, varr1)
If tot = Range("A1") Then
iCol = iCol + 1
If iCol = 255 Then
MsgBox "too many columns, i is " & i & " of " & num & _
" combinations checked"
Exit Sub
End If
rng.Offset(0, iCol) = varr1
End If
Next
End Sub

***or***

Enter
8
6
3
2
6
10
9
4
12
8
6
1
8
10
8
14
10
9
12
12
14
6
4
3
4
4
4
0
6
10
4
9
6
3
11
12
10
7
12
8
8
in Cells A1:A41

90
in cell B1

Run this macro:
Sub FindSeries()

Dim StartRng As Range
Dim EndRng As Range
Dim Answer As Long
Dim TestTotal As Long

Answer = Range("B1") '<<< CHANGE

Set StartRng = Range("A1")
Set EndRng = StartRng
Do Until False
TestTotal = Application.Sum(Range(StartRng, EndRng))
If TestTotal = Answer Then
Range(StartRng, EndRng).Select
Exit Do
ElseIf TestTotal Answer Then
Set StartRng = StartRng(2, 1)
Set EndRng = StartRng
Else
Set EndRng = EndRng(2, 1)
If EndRng.Value = vbNullString Then
MsgBox "No series found"
Exit Do
End If
End If
Loop
End Sub




--
BeachLover


"Gord Dibben" wrote:

Have a look at Tushar Mehta's site for some help on Solver and code.

http://www.tushar-mehta.com/excel/te...ues/index.html

See the links to google threads also at the bottom of the page.

BUT..........................You should read this from Harlan Grove before
embarking on your project.

http://tinyurl.com/a6xzff



Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 15:50:01 -0800, BeachLover
wrote:

Thank you for the information. I went to Tools, Add-Ins, and checked Solver
Add-ins and then clicked ok but it said it this add-in and it worked. It is
now on my tool bar. However, I have no idea how to use it. I know you said
to google for examples but I have not had much luck with that. Do you or
anyone else have experience with Solver to help me with my diliemma.