out of this given data i want set of such elements whose sum is such that the difference between the sum and 84 is minimum or zero.
40.5 33.5 34.5 45 46 47.75 49 30 32 34 37 39 46 41 30 34 37 39 51
please help me out in writing a suitable code or formula for this..... I really need this....
Excel query



Re: Excel query
39+45 = 84
8484 = 0Not sure how'd you'd do it in excel though

Re: Excel query
Hi nivsam1210,
Nice to meet you.
Would you look at attatched?
http://www.mrexcel.com/pc09.shtml
Regards, junho 
Re: Excel query
one more approach is to use SOLVER...you may have to set the changing cells crietrion to binary

Re: Excel query
Hi,
Can you in detail tell me how to use the solver for this problem.....
Many thanks 

Re: Excel query
this assumes you have SOLVER as an addin already implemented....
1. Assume all your numbers are in Col A..asume there are 20 of them filling rows 1 to 20
2. in C1 put this formula =A1*B1 and copy till row 20
3. In C21 put this formula = ABS(84SUM(C1:C20)).....In B21 put =SUM(B1:B20)
4. Now set up SOLVER.....the target cell is C21...the constraint is to minimize.... changing cell is range B1:B20 subject to the following constraints Range B1:B20 is binary and Cell B21 is greater than or equal to one.....then solve and you ought to get the optimum solution
pl note this is a very basic approach to problem setup and depending upon the data layout, constrainst and objective the problem can further be refined and optimised
hth 
Re: Excel query
Hi Pangolin,
Thanks a lot for ur help,
I do have slover in excel, but the thing is the formula that u have given me to put in C21, i put it in solver set target, but the solver is not accepting thr formula, i guess i am making a typing error or something, can you please guide me how to put it exactly. i am typing ABS(84SUM(C1:C20)) in C21 But when i put C21 in set target cell in solver it is prompting me that "set target must contain a formula"............ what should i do????????
and what should i do with cell B1 to B20, and in C1 to C20 i should have A1*B1, A2*B2, A3*B3.....etc ryt?????
Ppplllleasee help....... 
Re: Excel query
obviously you have to type =ABS(84SUM(C1:C20)) and not ABS(84SUM(C1:C20))......no wonder SOLVER is giving you such an error......read the steps in the earlier post again and do in the manner suggested........rest assured that it works for me
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!