Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scenario?
I have a list of numbers (approximately 3000) that sum to X. However, my
control figure is Y. Is there a formula that will evaluate the list of 3000 numbers and return those that when summed, equal the difference between X and Y? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scenario?
You could do this with Solver but with 3000 number, it is unlikely that
there is a unique solution. Do you care? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I have a list of numbers (approximately 3000) that sum to X. However, my control figure is Y. Is there a formula that will evaluate the list of 3000 numbers and return those that when summed, equal the difference between X and Y? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scenario?
No, I don't care. At least that would narrow down the field, then I could
take it from there. "Bernard Liengme" wrote: You could do this with Solver but with 3000 number, it is unlikely that there is a unique solution. Do you care? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I have a list of numbers (approximately 3000) that sum to X. However, my control figure is Y. Is there a formula that will evaluate the list of 3000 numbers and return those that when summed, equal the difference between X and Y? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scenario?
Assuming your numbers are in A1:A3000
In B1:B3000 enter 1 C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2) In Solver Changing cells B1:B3000 Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1) Solve Does it work with your data? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... No, I don't care. At least that would narrow down the field, then I could take it from there. "Bernard Liengme" wrote: You could do this with Solver but with 3000 number, it is unlikely that there is a unique solution. Do you care? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I have a list of numbers (approximately 3000) that sum to X. However, my control figure is Y. Is there a formula that will evaluate the list of 3000 numbers and return those that when summed, equal the difference between X and Y? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scenario?
I really appreciate your help.
After inputting the information as described, once I hit "solver" I receive a message that says "too many adjustable cells". Any thoughts? "Bernard Liengme" wrote: Assuming your numbers are in A1:A3000 In B1:B3000 enter 1 C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2) In Solver Changing cells B1:B3000 Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1) Solve Does it work with your data? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... No, I don't care. At least that would narrow down the field, then I could take it from there. "Bernard Liengme" wrote: You could do this with Solver but with 3000 number, it is unlikely that there is a unique solution. Do you care? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I have a list of numbers (approximately 3000) that sum to X. However, my control figure is Y. Is there a formula that will evaluate the list of 3000 numbers and return those that when summed, equal the difference between X and Y? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scenario?
Play with a smaller set of data and see if that works.
If so we can try to work out something -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I really appreciate your help. After inputting the information as described, once I hit "solver" I receive a message that says "too many adjustable cells". Any thoughts? "Bernard Liengme" wrote: Assuming your numbers are in A1:A3000 In B1:B3000 enter 1 C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2) In Solver Changing cells B1:B3000 Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1) Solve Does it work with your data? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... No, I don't care. At least that would narrow down the field, then I could take it from there. "Bernard Liengme" wrote: You could do this with Solver but with 3000 number, it is unlikely that there is a unique solution. Do you care? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I have a list of numbers (approximately 3000) that sum to X. However, my control figure is Y. Is there a formula that will evaluate the list of 3000 numbers and return those that when summed, equal the difference between X and Y? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Scenario?
I narrowed the data down to about 25 figures. Again, I'm trying to
determine, of these figures, which of these when summed adds to X. I used the solver listed below on the 25 figures, however I'm not sure I understand the results. Is there a possibility I could email you a sample file? "Bernard Liengme" wrote: Play with a smaller set of data and see if that works. If so we can try to work out something -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I really appreciate your help. After inputting the information as described, once I hit "solver" I receive a message that says "too many adjustable cells". Any thoughts? "Bernard Liengme" wrote: Assuming your numbers are in A1:A3000 In B1:B3000 enter 1 C1 =SUMPRODUCT(A1:A3000, B1:B3000), C2 = Y (your target), C3 = ABS(C1-C2) In Solver Changing cells B1:B3000 Constraints C3=0 and B1:B3000 bin (binary - i.e. 0 or 1) Solve Does it work with your data? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... No, I don't care. At least that would narrow down the field, then I could take it from there. "Bernard Liengme" wrote: You could do this with Solver but with 3000 number, it is unlikely that there is a unique solution. Do you care? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "simmerdown" wrote in message ... I have a list of numbers (approximately 3000) that sum to X. However, my control figure is Y. Is there a formula that will evaluate the list of 3000 numbers and return those that when summed, equal the difference between X and Y? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Scenarios and Scenario Manager | Excel Worksheet Functions | |||
why report manager ignores the range set in the scenario | Excel Discussion (Misc queries) | |||
Scenario Results all the same | Excel Worksheet Functions | |||
Excel Scenario Manager should let me re-order scenarios (in the s. | Excel Discussion (Misc queries) |