 # Excel query

• 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....

• Re: Excel query

39+45 = 84
84-84 = 0

Not sure how'd you'd do it in excel though

• 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 add-in 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(84-SUM(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 set-up and depending upon the data lay-out, 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(84-SUM(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(84-SUM(C1:C20)) and not ABS(84-SUM(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!