Graphing relationship between 3 variables

[aposatsk]

I have a data sheet which depends on x, y, and z. Therefore, the
function is something like z = cx + dy, where c and d are constants and
x and y may have exponents.

I don't know, however, how to graph this sort of data in excel. The
surface graph seems really dissatisfactory, and does not offer me the
option of seeing the equation the data makes.

My values are like this:

X--------------Y----------------Z
20-------------0-------------$1,000,232.51
15-------------5-------------$5,022,111.11
15-------------3-------------$2,333,444,34
20-------------3-------------$1,951,114.25

I need to see how X and Y affect Z, figure out the equation of this
surface, and then take partial derivatives to figure out the optimal
values for X and Y to maximize Z.

 

[MrShorty]

From what I've been able to gather, Excels 3D charts are limited to
category type charts, they aren't offered as scatter plots. If you
must have a 3D plot of the function, you might want to get a different
utility to generate the plot for you.

As for the optimization part of the problem, that shouldn't be too
difficult. It's been a while since I took multi-variable calculus, but
it seems like it was as simple as setting grad z = 0 and solving the
resulting system of equations. There isn't much calculus built into
Excel, but if the user knows the calculus, he/she can usually get Excel
to do the calculations.

Which leaves the middle part of the problem: fitting the data to a
curve of the form z=cx^a+dy^b. As linear parameters, c and d (at a
given a and b) should be pretty easy to determine using the LINEST
function. I'm not sure how you'd want to approach the non-linear
parameters a and b. On occasion, I've used Solver to fit non-linear
parameters like that. Other cases will have a limited set of values
the exponent can assume (say 1,2 or 4) and the user picks the exponent
that gives the best fit.

hope that helps. If you get stuck on something, give us the specific
part you are stuck on and we'll try to help further.