How can I?

I have a list of 32 numbers ranging from 166.80 to 4,231.22 in cells
R1:R32; there may be duplicates but there are no negatives and all are
to 2 decimal places.

I also have a total of 16,496.42

Is there a way finding a combination of cells within the range R1:R32
that equals the total of 16,496.42?

Thanks for any responses,
Dave Moore

"DaveMoore" wrote:
Is there a way finding a combination of cells within
the range R1:R32 that equals the total of 16,496.42?

Yes. But you might have to run through as many as 4,294,967,295 (2^32-1)
combinations to find at least one. And unless you want to try this
manually, you would need to write a macro or UDF.

Moreover, even if there is a combination that sums to 16,496.42 on paper,
the computer sum might not be exactly that. The reason: most numbers with
fractional digits cannot be represented exactly by Excel (and most
applications). That is why, for example, IF(10.1-10=0.1,TRUE) results in
FALSE(!).

That problem is compounded by the fact that unless R1:R32 contains constants
or you were careful to explicitly round the value in each cell, by using
either ROUND or the "Precision as displayed" option, the numbers that
__appear__ to have only 2 decimal fraction digits may have more.

So it would behoove you to look not for equality with 16,496.42, but for an
approximation. There are several ways to do that. I prefer to explicitly
round the trial sum before doing the comparison.

However, in real life, we do not know that there is a such a combination.
So typically, we look for the combination that has the closest result to
16,496.42. That does require that we look at all 4,294,967,295
combinations, unless we find "equality" (approximately ;-) first.

With all that in mind, are you still interested?


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

"DaveMoore" wrote in message
...
I have a list of 32 numbers ranging from 166.80 to 4,231.22 in cells
R1:R32; there may be duplicates but there are no negatives and all are
to 2 decimal places.

I also have a total of 16,496.42

Is there a way finding a combination of cells within the range R1:R32
that equals the total of 16,496.42?

Thanks for any responses,
Dave Moore

Hi,
take a look here

http://www.tushar-mehta.com/excel/te...e_combinations

"DaveMoore" wrote:

I have a list of 32 numbers ranging from 166.80 to 4,231.22 in cells
R1:R32; there may be duplicates but there are no negatives and all are
to 2 decimal places.

I also have a total of 16,496.42

Is there a way finding a combination of cells within the range R1:R32
that equals the total of 16,496.42?

Thanks for any responses,
Dave Moore

.

Absolutely!
I could easily use the ROUND function to 2 decimal places if that
would help matters. I am fairly confident that in this particular
case a combination of these cells will equal 16,496.42 but may not at
other times.
Can you help further?
Many Thanks,
Dave Moore


On 9 Feb, 19:28, "Joe User" joeu2004 wrote:
"DaveMoore" wrote:
Is there a way finding a combination of cells within
the range R1:R32 that equals the total of 16,496.42?

Yes. *But you might have to run through as many as 4,294,967,295 (2^32-1)
combinations to find at least one. *And unless you want to try this
manually, you would need to write a macro or UDF.

Moreover, even if there is a combination that sums to 16,496.42 on paper,
the computer sum might not be exactly that. *The reason: *most numbers with
fractional digits cannot be represented exactly by Excel (and most
applications). *That is why, for example, IF(10.1-10=0.1,TRUE) results in
FALSE(!).

That problem is compounded by the fact that unless R1:R32 contains constants
or you were careful to explicitly round the value in each cell, by using
either ROUND or the "Precision as displayed" option, the numbers that
__appear__ to have only 2 decimal fraction digits may have more.

So it would behoove you to look not for equality with 16,496.42, but for an
approximation. *There are several ways to do that. *I prefer to explicitly
round the trial sum before doing the comparison.

However, in real life, we do not know that there is a such a combination.
So typically, we look for the combination that has the closest result to
16,496.42. *That does require that we look at all 4,294,967,295
combinations, unless we find "equality" (approximately ;-) first.

With all that in mind, are you still interested?

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

"DaveMoore" wrote in message

...



I have a list of 32 numbers ranging from 166.80 to 4,231.22 in cells
R1:R32; there may be duplicates but there are no negatives and all are
to 2 decimal places.

I also have a total of 16,496.42

Is there a way finding a combination of cells within the range R1:R32
that equals the total of 16,496.42?

Thanks for any responses,
Dave Moore- Hide quoted text -

- Show quoted text -

DaveMoore -

"Looking for a specific sum within a range of numbers... "

http://www.mrexcel.com/forum/showthread.php?t=49138

- Mike
http://www.MikeMiddleton.com



"DaveMoore" wrote in message
...
I have a list of 32 numbers ranging from 166.80 to 4,231.22 in cells
R1:R32; there may be duplicates but there are no negatives and all are
to 2 decimal places.

I also have a total of 16,496.42

Is there a way finding a combination of cells within the range R1:R32
that equals the total of 16,496.42?

Thanks for any responses,
Dave Moore