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

buddy1000

Active Member
Joined
Oct 31, 2007
Messages
294
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Oct 31, 2007
Messages
294
I tried it but the final value solver gives is the same value in T1 and T3.
 

buddy1000

Active Member
Joined
Oct 31, 2007
Messages
294
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
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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
Joined
Oct 31, 2007
Messages
294
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
Joined
Oct 31, 2007
Messages
294
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
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top