excel 2007 solver function

Hi all,

I'm trying to get my solver to work on a problem but I'm having a hard time getting the formulas to work for solver.

The goal is to maximize the SUM of the area under Y curve but the time range is dependent on a function.

My table is setup like this: X, Y, A

X is time
Y is distance
A is area under curve for Y distance at X time

This is where I start to get stuck...

Here is ideally how it should work:

Start time = X1 through X30, Excel trys each value in column X
End time = based on a function and Start time (will match some value in X1 through X30 once rounded)
Maximize Sum A (sum up the values in A for the solved range)

Here is my little example:

Start time is X15 (excel will eventually try X1 through X30 once solver runs and maximizes A)
End time works out to be X10 after applying the function (it will change when excel trys different start times)
Sum(A10:A15) (this sum range will also change when excel trys different start times)


I can't figure out how to get the solver to do something like this. I've attached my spreadsheet in case my rambling is hard to understand. Any help is much appreciated!

shg,

Thanks for the input!

After looking through the code, I might have misexplained myself. If I brute force what I'm trying to get excel to automate, in one way or another, I get a maximized sum at time = 6600 (if I recall correctly).

Basically to brute force the calculation this is what I do (using your table for my numbers):

time drop percentage = .614

Pick a time (anything in column B6 through B52), say B34 = 6600
delta time = B34 - (B34 * .614) = 2620
new time after change = 6600 - 2620 = 4180 (round this number to a whole number to match a time in the table) so you get 4200 which matches up to B22
Now I finally SUM(D34:D22) = the sum of the area under the curve for the range.

I get mixed up because the range varies based on a percentage and I can't figure out how to get excel to recongnize that and then sum up the adjacent columns.


Much appreciated!

Shg,

You're the man! Thanks so much!

I had one more quick question. What excel function could I use to to say what the time was at the maximum value of the area under the curve?

Say time is Ax and the maximum valve of the area is C, how could I get an excel cell to say Ax based on MAX(Cx:Cx)?


Much appreciated!

Again, shg, you're the excel man