Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Graphing relationship between 3 variables
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. -- aposatsk ------------------------------------------------------------------------ aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709 View this thread: http://www.excelforum.com/showthread...hreadid=572683 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Graphing relationship between 3 variables
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. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=572683 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble Graphing 1 point w/ 2 variables | Charts and Charting in Excel | |||
RE-submitting of: constructing (complex) variables with worksheet functions | Excel Worksheet Functions | |||
Bubble Charts with two non-numerical variables | Excel Worksheet Functions | |||
VBA reseting variables | Excel Discussion (Misc queries) | |||
Graphing weekly/monthly averages | Charts and Charting in Excel |