Calculating nearest value from a small range of values

[russ_g_uk]

Hi,

Just wondering if there is a way to do this. I'm no expert so forgive me if it is an easy solution!

If I have a list of 6 values and a number that I want to get as near to as possible (above or below) using any, and as many as needed, of these 6 values - is there a way of calculating the nearest value? And, if there is, is there also a way of showing which values would be used?

Example:
Value 1 = 0.190
Value 2 = 0.278
Value 3 = 0.593
Value 4 = 1.079
Value 5 = 2.075
Value 6 = 2.998

Value I want to get as near to as possible = 3.644

Any help would be really appreciated

Thanks,

Russ

[shg]

You could use Solver:

	A	B	C
1	Qty	Value
2	1	0.190
3	1	0.278
4	0	0.593
5	1	1.079
6	1	2.075
7	0	2.998
8
9	Sum	3.622	B9: =SUMPRODUCT(A2:A7, B2:B7)
10	Target	3.644	B10: Input
11	Error	0.022	B11: =ABS(B9-B10)

Set B11 to Min by changing A2:A7, with A2:A7 constrained to bin(ary)

[russ_g_uk]

That works a treat

Now, is it possible to have 15 target values but keeping the same 6 values to choose from?

Thank you for your help

[shg]

Short of writing VBA to do it, do it for each target value.

[russ_g_uk]

OK, VBA is a bit too advanced for me at the moment

What I've just been looking to see if I can do is copy the same data to 15 worksheets, each with a different target value. That way I can run solver on each sheet and the parameters in solver are correct. I also just tried to create a macro which automatically ran solver on sheet 1, switched to sheet 2, ran solver on that etc etc. But I failed!!!

At least you have got me to a stage where I can get near the targets, thank you

[shg]

You're welcome.

[russ_g_uk]

Is there any way of changing the solver parameters to get the error as near to zero as possible, even if this means the value becoming negative (eg -0.21 is nearer to zero than +0.26)? This would give me a better outcome if I could do it that way.

[russ_g_uk]

Ive just hit a snag with this! All was working fine on Excel 2003. I've just been upgraded to 2010 and the solver doesn't work in the same way (ie it's not giving me binary values). Can this be sorted?

[russ_g_uk]

Sorry to post on my old thread but just wondered if somebody had any ideas why solver isn't working the same in 2010 as shown in my last post?

[Timboson]

You need to load the Solver add-in to Excel 2010,

Load the Solver Add-in

The Solver add-in is a Microsoft Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first.
1.Click the File tab, and then click Options.
2.Click Add-Ins, and then in the Manage box, select Excel Add-ins.
3.Click Go.
4.In the Add-Ins available box, select the Solver Add-in check box, and then click OK.
1.Tip If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.
2.If you get prompted that the Solver add-in is not currently installed on your computer, click Yes to install it.
5.After you load the Solver add-in, the Solver command is available in the Analysis group on the Data tab.

[russ_g_uk]

Hi, solver is loaded, it just works differently in 2010! If you look at the example set above, the values it changes are in A2:A7 with those values constrained to binary. That worked fine in 2003 but in 2010 its not changing the values to binary. Instead it's changing each value to small numbers (e.g. 0.63824) which is not how I need it to be.

[Alf]

but in 2010 its not changing the values to binary.

In Excel 2010 check "Options" in Solver (tab "All Methods") see if the box marked "Ignore Integer Constraints" is ticked if so untick.

Alf

[russ_g_uk]

Alf, you're a star! Something so simple and I'm working again now.

Thank you

[Alf]

Glad to be of help.

Alf, you're a star!

Well not really (not according to my wife at least) but you can give me one by klicking on the small star bottom left in my post.

As this seems to solve you last problem don't forget to mark the thead "Solved" as per forum rules.

Alf

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved