An optimization problem

[Paul Smith]

Dear All

Consider the following Excel sheet:

A B C D E
1 1 1 1 1
2
3 14 72 35 72 1.93
4 99 16 89 24 2.28
5 72 29 70 49 2.2
6 96 1 62 60 2.19
7 27 33 95 44 1.99
8 3 80 78 62 2.23
9 6 41 82 79 2.08
10 2.13

I would like to know whether Excel is able to solve the maximization
problem described in what follows. In E10, the formula is

=average(E3:E9)

and I want to maximize it by choosing the appropriate non-negative
numbers in A11, subject to the constraint

A1+B1+C1+D1 = 4

In E3, I have

=sumproduct($A$1:$D$1;A33)/100

and this formula is copied in column up to E9.

Thanks in advance,

Paul

 

[Bernard Liengme]

You say you want to maximize =average(E3:E9)
What are we allowed to change?
The values in E3:E9 or the range of cells used by average?
Not sure I understand the constraint - row 1 does not feature in the
formula.
best wishes

 

[Paul Smith]

You say you want to maximize =average(E3:E9)
What are we allowed to change?
The values in E3:E9 or the range of cells used by average?
Not sure I understand the constraint - row 1 does not feature in the
formula.

We are allowed to change the values in A11, which, in turn, change
the values in E3:E9.

Paul

 

[micha_d]

Dear All

Consider the following Excel sheet:

A B C D E
1 1 1 1 1
2
3 14 72 35 72 1.93
4 99 16 89 24 2.28
5 72 29 70 49 2.2
6 96 1 62 60 2.19
7 27 33 95 44 1.99
8 3 80 78 62 2.23
9 6 41 82 79 2.08
10 2.13

I would like to know whether Excel is able to solve the maximization
problem described in what follows. In E10, the formula is

=average(E3:E9)

and I want to maximize it by choosing the appropriate non-negative
numbers in A11, subject to the constraint

A1+B1+C1+D1 = 4

In E3, I have

=sumproduct($A$1:$D$1;A33)/100

and this formula is copied in column up to E9.

Thanks in advance,

Paul

this is a very typical task for the Excel-Solver
enter in E1 =sum(a1:d1)

use from menu Tools- Solver
enter in target cell: $E$10
select equal to 'Max'
By Changing Cells: $a$1:$D$1
Subject to Constraints:
$A$1>=0
$B$1>=0
$C$1>=0
$D$1>=0
$E$1=4

enter 'Solve' - thats it...

Regards - Michael

PS: in case you do not find the entry "Solver" in you menu Tools, you
need to add the "Solver-Add-In" from the Tools-Add-In Menu..

 

[Paul Smith]

this is a very typical task for the Excel-Solver
enter in E1 =sum(a1:d1)

use from menu Tools- Solver
enter in target cell: $E$10
select equal to 'Max'
By Changing Cells: $a$1:$D$1
Subject to Constraints:
$A$1>=0
$B$1>=0
$C$1>=0
$D$1>=0
$E$1=4

enter 'Solve' - thats it...

Thanks, Michael. That is it!

Paul

 

[Dana DeLouis]

Hi. If your problem gets larger, one Solver technique might be the
following:

Eliminate your extra E1 equation.
Change D1 to =4-Sum(A1:C1)

Your model therefore can be:

Max: E9
Change: A1:C1
Subject To:
D1 >= 0

** Solver options:
Select "Assume non-negative"

Without any other constraints, the solution will be the 3rd column.
The 3rd column has the largest Average.
Again, just a technique.

 

[Harlan Grove]

Consider the following Excel sheet:

A B C D E
1 1 1 1 1
2
3 14 72 35 72 1.93
4 99 16 89 24 2.28
5 72 29 70 49 2.2
6 96 1 62 60 2.19
7 27 33 95 44 1.99
8 3 80 78 62 2.23
9 6 41 82 79 2.08
10 2.13

I would like to know whether Excel is able to solve the maximization
problem described in what follows. In E10, the formula is

=average(E3:E9)

Note that this is the same as maximizing SUM(E3:E9).

and I want to maximize it by choosing the appropriate non-negative
numbers in A11, subject to the constraint

A1+B1+C1+D1 = 4

In E3, I have

=sumproduct($A$1:$D$1;A33)/100

and this formula is copied in column up to E9.

Get rid of more constants. You could just use

=SUMPRODUCT($A$1:$D$1;A33)

in cell E3 filled down. So you want to maximize

=SUMPRODUCT(A11*A3:E9)

which is equivalent to maximizing

=A1*SUM(A3:A9)+B1*SUM(B3:B9)+C1*SUM(C3:C9)+D1*SUM(D39)

subject to SUM(A11)=4.

If this problem is really this simple, the answer is to find the
column in A3:E9 with the largest sum, then put 4 into the
corresponding cell in A11 and 0 into the other cells in A11. You
could use the array formula

=4*(MATCH(MAX(MMULT(TRANSPOSE(ROW(A39)^0),A39)),
MMULT(TRANSPOSE(ROW(A39)^0),A39),0)=COLUMN(A11))

in A11. No need for Solver. [A little algebra goes a LONG way!]