# is this POSSIBLE???? (solver/goal seek???)

#### buddy1000

##### Active Member
Morning....How van I achieve the following...

column C, D and column E has different numerical values.
column D is calculated on the basis of cell T1 and column E on the basis of cell T3.
Colum C is just hard input.
Column F is a value (4 or -4) calculated on the basis of column D and column E.
T1 and T3 can only be between 1 - 100
Column G is calculated as follows...ex. G10 is calculated using values from C9, F9, F8 and G9.

Assume G100 is ex. 500.

Changes in T1 and T3 will ultimately lead to a change in G100.
How can I alter T1 and T3 in order to give the largest value for G100.

So I need to find the combination for T1 and T3 that will give the largest value for G100.

any ideas?

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Andrew Poulsom

##### MrExcel MVP
You can probably use Solver to set G10 to Max by changing cells T1,T3 with the constraints that T1 and T3 must be greater than or equal to 1 and less than or equal to 100.

#### buddy1000

##### Active Member
I tried it but the final value solver gives is the same value in T1 and T3.

#### buddy1000

##### Active Member
Is it by the way possible to send the sheet (small file) to someone because I tried it several times this past week and for some reason solver just gives a the final value in T1 and T3. If i change T1 and T3 and use the solver it give sthe new value sin T1 and T3

#### Andrew Poulsom

##### MrExcel MVP
It worked for me when I tried it, but I don't know your formulas. I see I said "G10 to Max" but it looks like it should be "G100 to Max". Did you try that?

To help you further I would need to know all your formulas and what ranges they cover.

#### buddy1000

##### Active Member
Yes Andrew..I did G100.

The problem is that I have a lot of formulas that all connect to each other in some fashion, however a change in T1 and/or T3 changes other cells and leads to a change in the value in G100

#### buddy1000

##### Active Member
Here are I think the most inportant formulas:

G100--> =IF(F98<>F99,(C99-B100)*\$M\$1/\$G\$5*10*F99+(C100-B100)*10*\$M\$1/\$G\$5*F99+G99,(C100-C99)*10*\$M\$1/\$G\$5*F99+G99)

F98-->=IF(D98>=E98, 4, -4)
F99-->IF(D99>=E99, 4, -4)
C99-->hard input (107.90)
B100-->hard input (107.74)
M1-->hard input (10,000,000.-)
G5-->hard input (100,000.-)
C100-->hard input (107.77)
G99-->(=IF(F97<>F98,(C98-B99)*\$M\$1/\$G\$5*10*F98+(C99-B99)*10*\$M\$1/\$G\$5*F98+G98,(C99-C98)*10*\$M\$1/\$G\$5*F98+G98))
[In this case it is 104,800.-]

D99-->=(D98+(\$Q\$1*(C99-D98)))
E99-->=(E98+(\$Q\$3*(C99-E98)))

Q1-->=2/(1+T1)
Q3-->=2/(1+T3)

So i tried to max G100 by changing T1 and T3 with solver

Last edited:

#### Andrew Poulsom

##### MrExcel MVP
With that scenario and both T1 and T3 set to 1, the formula in G100 returns 431,080. Changing T1 to any value greater than 1 results in G100=430,840. Increasing T3 has no effect on the result. Increasing both T1 and T3 results in G100=431,080 provided that T3 is greater than or equal to T1, otherwise G100=430,840. So there are only 2 possible results for G100 and it is already maximised.

That said, I couldn't get Solver to work with G100=430,840, so I think you are out of luck.

Replies
6
Views
76
Replies
12
Views
509
Replies
5
Views
145
Replies
3
Views
262
Replies
21
Views
384

1,172,042
Messages
5,878,867
Members
433,380
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back