Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching a range of numbers for a specified total
I know that Excel has a way to search a range of numbers and try all
possible combinations in that range to see if any of the numbers in the range equal a specified total. For example (a very simple example), column A, with the numbers 102, 209, 315, 483 and 562. How do we get Excel to search column A for the combination of cells that would equal 524? I also know this requires solver, but am unsure of how to manipulate solver to perform this task. Any tips would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching a range of numbers for a specified total
Search Google Groups for the following thread:
function to find which cells add up to a given total? That should get you started. - John Michl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching a range of numbers for a specified total
The suggested thread posted by John shows an excellent setup for the
Solver. However, this problem is a variant of the k-values problem (If I remember the term correctly from my algorithms class), which is NP-complete and too much for poor Solver if you have too many rows. I tried it with 15 cells and the Solver found a solution quickly. I tried it with 30 and it could not. A very simplistic randomized optimization algorithm will try various combinations until you hit the correct one. There is no guarantee how long it will take. For N numbers we have a total of 2^N combinations. However, it will find something. Then again, it might be better to try exhaustive search, but for the moment this is the VBA code I am suggesting: Sub FindNumbers() Do Application.Calculation = xlCalculationManual For i = 1 To 30 Cells(i, 2) = Int(Rnd() * 2) Next i Application.Calculation = xlCalculationAutomatic Loop Until Range("D1") = 1124 End Sub This macro supposes the same setup as in the thread, for numbers in A1:A30 and 0/1's in B1:B30. The 1124 is the target sum. HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Part Numbers In A Range | Excel Worksheet Functions | |||
Adding numbers in one cell and showing total in seperate cell | Excel Discussion (Misc queries) | |||
Adding numbers to a running total | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
How to total items if they fall between a date range | Excel Discussion (Misc queries) |