Q on how to find best combination of values

[Brian Bucher]

Greetings all,
I'm trying to find a way (an algorithm) for determining this:

Maximize the value of A
Keep B < 10

Data:
A B
F1 9 4
F2 11 6
F3 10 6
F4 10 3

F1 is 'fraction 1'
I can do this manually in Excel, but when I go to larger data sets it'd
be a huge pain. Does anyone know of an algorithm or a way in Excel that
I could easily do this? I'm thinking that it'd take some macro programming
in order to do it, but first I need an algorithm. Even if I couldn't do
it with Excel/macros I'd still like to know how to do it. It's been a long
time since I've done much programming.

Thanks for any input or pointers to good info,
Brian

 

[Ken Wright]

Have you looked at Solver (Tools / Solver - It's an addin you may have to enable). This will
allow you to set constraints and then give you the result you need subject to those constraints.

 

[Brian Bucher]

Have you looked at Solver (Tools / Solver - It's an addin you may have to enable). This will
allow you to set constraints and then give you the result you need subject to those constraints.

Hi Ken and thanks for the response,

I have looked at Solver but I don't think it'll help. As far as I
understand, it works by only varying the contents of a/some cell(s) in
order to find a min/max/target-value. Since I'm trying to find the
maximum of A by _combining_ fractions within the constraint of B (avg)
< 10 I don't think Solver can help me..but I could be wrong.

Regards,
Brian