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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,789
Members
448,297
Latest member
carmadgar

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
Back
Top