If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
How can I?
"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 |
#3
|
|||
|
|||
How can I?
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 . |
#4
|
|||
|
|||
How can I?
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 - |
#5
|
|||
|
|||
How can I?
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 |
Thread Tools | |
Display Modes | |
|
|