Solver is too slow using LARGE function

I have a data array:

A B C D
1 75 84 96 45
2 56 99 74 32
3 32 93 56 55

Which I rank using the RANK function:

A B C D
4 3 1 3 2
5 2 3 2 1
6 1 2 1 3

I then set-up a 3rd array of weighted values:

A B C D E F
7 0 0 0 0 sum=(A7:D7) =E7/$E$10
8 0 0 0 0 sum=(A8:D8) =E8/$E$10
9 0 0 0 0 sum=(A9:D9) =E9/$E$10
10 sum=(E7:E9)


which is computed by 4 variables such that given:

A B C D
10 x1 x2 x3 x4

then:

A7 = A4 * $A$10
B7 = B4 * $B$10 and so forth

I then sum the rows of the weighted array and calculate the % of total row sum.

Finally, I use Solver to calculate (Evolutionary) a max weighted row % in any order I define.

Specific example: Let's say I want to solve for F8 > F9 > F7

I would set up Solver to max F8
using variables (A10:D10)

subject to these constraints:
all variables >= 0
all variables <= 10
sum of all variables = 10
F8 = LARGE(F7:F9,1)
F9 = LARGE(F7:F9,2)
F7 = LARGE(F7:F9,3)

This system works great with simple arrays. Can anyone suggest a method that is FAST on very large arrays with many variables to compute?

Thanks!

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hopefully a sample file is attached to this message.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

As you have changed the title of you thread as per forum Guru's request (strange by the way as the other postings in your thread did not get upgraded with the new title) I'll add some comments.

First of all the "evolutionary" engine used in this solver setup is not one I'm that familiar with so I don't know how helpful my comments could be.

Under options you have set the time for solver to run as 999999999999 seconds. Could that be the reason why solver is slow? I used a setting of 15 seconds and I still got a solution. You could probably reduce this time setting a bit more to get the result faster.

As the range "C4:G4" is set with upper and lover constraint you could use the option "Require Bounds on Variables" If this improves things I don't know, you will have to test and see.

The setting for cells in range I18:I22 where the result should be "=LARGE(I18:I22, 1)" I changed to "<=LARGE(I18:I22, 1)" as your original setting made solver unable find a solution where all constraints were fulfilled.

With the new setting solver fond a solution where all constraints were satisfied.

Alf