1

I have been struggling to create a general equation for a data set with two dependent variables. I have been trying to use excel to model the equation but I am unable to match my data set. Using trendlines I am able to determine that the data set can be modeled using power functions. After some research it looks like I need to do a Multivariate Power Function Regression Analysis on my data set. Any help would be appreciated.

The data set is provided below.

Data Set 1

Piotr Gal
  • 11
  • It's hard to tell from your post what your variables are, where they are stored in the data, what they mean, etc. Also, what question are you trying to answer with your analysis? – Isabella Ghement Jun 23 '18 at 02:03
  • I am trying to use the data above to make an analytical model to calculate cycle rates of a machine. In the data set the x axis is length of the product in millimeters and the y axis is the machine acceleration in units of G (9.8m/s^2). That data set is represented as measured cycle rates. I want to be able to find a general equation in which i can input both x and y variables to determine the cycle rate but i am unaware if i would be able to do so in excel. – Piotr Gal Jun 24 '18 at 19:28

1 Answers1

2

Thanks for your clarifications, Piotr. I think it would be better if you set up your data differently in anticipation of your modeling. Something like this: 

Length  Acceleration Cycle_Rates
 50         0.2         135.4 
 50         0.3         161.3 
 .           .            .
 .           .            . 
 600        3.0         168.1

Then your could start by doing some exploratory plots of the data:

  1. Scatterplot of Cycle_Rates versus Length;
  2. Scatterplot of Cycle_Rates versus Acceleration;
  3. 3-Dimensional Scatterplot of Cycle_Rates versus Length & Acceleration.

These plots should give you some indication as to whether you could use multiple linear regression to model Cycle_Rates as a function of Length and Acceleration. (For this type of model to make sense for your data, you would like to see either a linear relationship or no relationship in the first two scatterplots, for example.)

One possible linear regression model would be: 

Cycle_Rates = beta_0 + beta_1*Length + beta_2*Acceleration + 
             beta_3*Length*Acceleration + error

But you would really have to look at plots of the data to see if this type of model would make sense in your situation. It's possible that the relationship between your input variables Length and Acceleration and your output variable Cycle_Rates is more complicated and may need to be captured by a nonlinear regression model.

Excel can be used to perform multiple linear regression - see, for instance, http://cameron.econ.ucdavis.edu/excel/ex61multipleregression.html.

To sum up, to find the general equation you are after, you must first find the appropriate statistical model (e.g., multiple linear regression). To this end, it will help if you re-arrange your data and examine some exploratory plots.

Isabella Ghement
  • 20,314
  • 2
  • 34
  • 58