How can I determine the best relationship for 3 variables, given several data points?

Question

What is the best way to determine the relationship for three apparently related variables? The relationship does not appear to be linear, and may follow a combination of non-linear functions.

I have the following data points:

x y z 1 0.5 0.01 1 1 0.01 1 2 0.01 1 10 0.01 1.3 0.5 0.015 1.3 1 0.0177 1.3 2 0.023 1.3 10 0.066 1.5 0.5 0.018 1.5 1 0.0223 1.5 2 0.031 1.5 10 0.1

Assume z is the output, and x and y is the input, and no variable can be 0.

1. Given these sample data points, how can I predict z given x and y?
2. Is there a mathematical relationship between the variables?
3. How can I find an equation that relates these variables?

Answer 1

I began by making a matrix of scatterplots of each variable against the other two. There is some association between z and each of the predictor variables. but apparently not between x and y. Correlations are as shown below.

 Correlations: x, y, z 

        x      y
 y  0.000
    1.000

 z  0.512  0.683
    0.088  0.014

The patterns of association in the scatterplots suggested it might be better to use $\log_e z$ than $z.$ Correlations are as follows. (The second number in each cell is a P-value, assuming normal data.)

 Correlations: x, y, logz 

           x      y
 y     0.000
       1.000

 logz  0.701  0.565
       0.011  0.055

Based on this information I did a regression of log z on x and y.

Data Display

 Row    x     y       z      logz
   1  1.0   0.5  0.0100  -4.60517
   2  1.0   1.0  0.0100  -4.60517
   3  1.0   2.0  0.0100  -4.60517
   4  1.0  10.0  0.0100  -4.60517  <-- possibly omit row (see below)
   5  1.3   0.5  0.0150  -4.19971
   6  1.3   1.0  0.0177  -4.03419
   7  1.3   2.0  0.0230  -3.77226
   8  1.3  10.0  0.0660  -2.71810
   9  1.5   0.5  0.0180  -4.01738
  10  1.5   1.0  0.0223  -3.80317
  11  1.5   2.0  0.0310  -3.47377
  12  1.5  10.0  0.1000  -2.30259

$ $ General Regression Analysis: logz versus x, y

 Regression Equation

 logz  =  -7.37403 + 2.46466 x + 0.105768 y

 Coefficients

 Term          Coef   SE Coef         T      P
 Constant  -7.37403  0.660649  -11.1618  0.000
 x          2.46466  0.509869    4.8339  0.001
 y          0.10577  0.027122    3.8998  0.004

 Summary of Model

 S = 0.362927     R-Sq = 81.08%        R-Sq(adj) = 76.88%
 PRESS = 3.28546  R-Sq(pred) = 47.57%

 Analysis of Variance

 Source      DF   Seq SS   Adj SS   Adj MS        F          P
 Regression   2  5.08093  5.08093  2.54046  19.2874  0.0005571
   x          1  3.07777  3.07777  3.07777  23.3667  0.0009288
   y          1  2.00315  2.00315  2.00315  15.2081  0.0036208
 Error        9  1.18545  1.18545  0.13172
 Total       11  6.26638

 Fits and Diagnostics for Unusual Observations

  Obs      logz       Fit    SE Fit   Residual  St Resid
    4  -4.60517  -3.85168  0.248492  -0.753487  -2.84857  R

  R denotes an observation with a large standardized residual.

Based on this printout it seems that $\log(x)$ can be predicted by the equation $\log(z) = -7.37403 + 2.46466 x + 0.105768 y.$ The P-values indicate that the constant term $-7.37403$ is significantly different from 0, and coefficients of $x$ and $y$ are significantly different from 1, all at the 1% level of significance. The diagnostics indicate that the fourth row of your data fit the regression model poorly (but I would say not so poorly that row of data should be ignored).

I also did a regression of z on x and y, obtaining the regression equation $z = -0.0714 + 0.0658421 x + 0.00466667 y$. However, all indications are that this is not quite as successful a fit as the one shown above.

The scatterplots of variables x, y, and log z are shown below. Associations are possibly useful for prediction, but (even with the log transformation of z) they are are only roughly linear.

So there is a useful relationship among the three variables making it possible to predict log z reasonably well from x and y. It is possible that some transformation of z other than taking logs would be better. It is also possible, especially with better knowledge of how the data were collected, that more useful predictions could be obtained by eliminating the fourth row of data from the regression. I will leave it to you to explore such variations.

If you are not familiar with linear regression using two predictor variables, you should read in your textbook about the methods and the cautions in interpretation. Computations and the graph are from Minitab statistical software, but other software should give essentially the same results.

Answer 2

A rough drawing of the points $(y,z)$ on a graph shows that $z(y)$ is quite linear for each one of the three values of $x$ : That is on the form $z\simeq Ay+B$

Again, a rough drawing of $(A,x)$ and $(B,x)$ on a graph shows that they are almost linear functions of $x$ : That is on the form $A\simeq a_1x+b_1$ and $B\simeq a_2x+b_2$

This draw us to consider the function $z \simeq (a_1x+b_1)y+(a_2x+b_2)$ which can be expressed as : $$z \simeq Axy+By+Cx+D$$

Then, we can proceed on a more accurate manner : A linear regression in order to evaluate the coefficients $A,B,C,D$ for the best fit according to the mean square deviation :

The mean absolute error ( https://en.wikipedia.org/wiki/Mean_absolute_error ) is : $$MAE=0.00032$$

In ADDITION :

The preliminary drawings made to observe the almost linear relationships in the given data :

This was a preliminary search which leads to the selected relationship $z \simeq Axy+By+Cx+D$. The numerical values appearing below are of no use for the computation of the coefficients $A,B,C,D$ as shown above.

Answer 3

BruceET's answer is pretty good. However, if the relationships between x, y, z are nonlinear, as you suspect, you may need transforms (x2, y2, xy, logx, lnx) when performing regression. I would start by plotting the three variables against each other and developing the model from there. If the scatterplots appear curved you know to include the transforms (Excel actually is good at curve fitting between two variables).