Help with Formula- Find all numbers in column that can add to a between value.

[PatrickShaw]

Trying to find a formula or Macro that can take a list of numbers then till me how many combinations of these numbers will equal a value that is between two other values , C >=B and <=A

[wfm007]

If possible attache a sample file

[PatrickShaw]

Attached is a sample of what I'm trying to work with I'm trying to find how many possible combinations of the values in coluumn A would be between 3.2million and 3.4 million.

[wfm007]

Put following formula in cell B3

=IF(AND(A3>=3200000,A3<=3400000),"Match","Not Match")

[PatrickShaw]

Doesn't quite do what I'm trying for I'm trying to find how many different ways the numbers in column A could add to number between 3.2 and 3.4 million

[loginjmor]

Hi -

This is a fairly difficult problem. There are several 2, 3, and 4 number combinations (and maybe even 5?) that could match your criteria. Looking at every single combination is somewhere around 18 million possibilities.

the following is a thread on this site that looked at a similar problem. It also referenced an external website. I'm not sure the thread was ever solved though.

http://www.excelforum.com/excel-form...ied-total.html

Hope this helps.

[MrShorty]

As noted, there are a lot of different possible combinations, and developing a robust algorithm for finding them all is going to be a bit of work. Here are some thoughts that might help get you started.

1) 1st thing I did was sort the list from low to high. The largest number is 3.5M -- there is nothing you could add this number to get a result between 3.2 and 3.4 million, so you know that this value will not be included in the solution set. 2nd largest is 3.2M, smallest is .75M, there's no way 3.2M is part of the solution. Continue until you find the i'th largest number that can be added to the smallest number to get something less than 3.4M
2) Now that you know the largest number that will be included in a pairwise combination, you can locate the range of values that will combine with it to get the result (those values that are between 3.4M-i and 3.2M-i). Continue in order with decreasing i until you start to repeat combinations.
3) Expand the algorithm to include 3 way, 4way, and higher combinations until you are satisfied that you have found all possible combinations. Some implementations would consider these a recursive expansion of the 2 way combination problem.