DaveMoore said:
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 -----
