Considering the equation below I need a software or Excel sheet that
will answer the following question: What value
of Y will maximize the value of Z given the 4 known values.  In other
words, I want a simple and fast way to find the Y that maximizes Z if
I give the 4 ?known values,? and I believe this can be done using
calculus to find the first derivative.  An excel sheet (or other
software) where I enter these 4 knowns and get Y and Z(maximized)
would be ideal.  I'd also like (but not required to answer the
question) to simultaneously run  numerous sets of
different ?known values? and to be able to see the Y and Z(maximized)
for each set of ?knowns.?  I don?t just want to be pointed
toward a software, I must be shown how to set this up in an excel
sheet step-by-step (or be given an excel sheet already setup) or be
given step-by-step instructions on how to set up another software
program to answer my equation.

Equation:
((known value 1)*((Y)*((known value 2)/(known value 3 +Y))))
-((Y*(1-((known value 2)/(known value 3 +Y))))*(known value
4))=Z[amount to maximize]

Given Values:
known value 1
known value 2
known value 3
known value 4

Find:
Y
Where Z is maximized

---

Request for Question Clarification by elmarto-ga on 06 Dec 2004 04:10 PST

Hi questionman!
It appears that your equation has no maximum. Replacing "known value
1" with 'a', "known value 2" with 'b' and so on, and then simplifying
some terms, we get that your equation is:

(a*b-d*b)*Y/(c+Y) - Y*d = Z

Notice that as Y approaches 'c', the first term's denominator goes to
zero, so the whole equation goes to infinity. Are you sure you posted
the equation correctly?

Best wishes,
elmarto

---

Request for Question Clarification by maniac-ga on 06 Dec 2004 18:30 PST

Hello Questionman26,

The comment by elmarto is basically correct though I would have stated that
  Y = -(known value 3)
would be an infinite result. However, I assume you have some
constraints for Y that would prevent this. If that is the case, please
state so one of us can walk you through the use of the "Solver" in
Excel to compute an optimal answer.

  --Maniac

The hardest part here is to get past your parentheses-rich equation.
It is Excel, allright.

Let a = known value 1
b = known value 2
c = known value 3
d = known value 4
and
x = b/(y+c)
and 
a,b,c,d are all positive numbers.

If I passed your equation, then,
z = a*y*x -y(1-x)*d
Simplifying that,
z = b(a+d)*[y/(y+c)] -d*y  ----(i)

If z is to be maximized, and since z is a function of y, then we find
y when dz/dy = z' = 0.

Differentiating (i) with respect to y,
z' = bc(a+d)/[(y+c)^2] -d
Setting that to zero,
y = +,-sqrt[bc(a+d)/d] -c  ----(ii)

-------------
To check if z'=0 would yield a maximum, we check z'' for the concavity
of the graph of z at y when z'=0.
z'' comes out as -2bc(a+d)/[(y+c)^3], which is always negative.
Meaning, z'=0 will always give a maximum z.
----------------

So there you are, if z is maximized, y is given by (ii).

I assume you are not interested on a negative y. Then, for your purpose,
y = sqrt[bc(a+d)/d] -c
Or, 
y = -c +sqrt[bc(a+d)/d]  -----(iii)

This (iii) might still give a negative y, if c is greater than the
sqrt[b*c*(a+d)/d]. Since you are not interested on a negative y, just
reject it. Play with other combinations of a,b,c,d to get a positive
y.

You should be able to do (iii) in Excel. It will be just a 5-column
Excel sheet. Just be careful with those parentheses.

Thanks ticbol, I'm very impressed and appreciative!  This is the
correct answer I was looking for.