CODEWITHSUNDEEP
statistics
VSC
VSC

Reputation: 407

How to calculate a multi-variable formula from a table of data

I have a table of several independent variables that I need to calculate a formula from to generate the dependent variable. Though trial I have come up with a value for the dependent variable. For example, I have a table like this:

x1 | x2 | x3 || z(value found by experiment)
-------------------
1  | 2  | 3  || 10
3  | 4  | 5  || 14
2  | 3  | 3  || 15
1  | 2  | 7  || 9

now I want a formula such that:

f(x1,x2,x3) = z

Now how do I go through and get the value? Can you point me to some resources?

I feel like I should remember this from my school days, but I dont.

Also: Do you know any tools that will do this for me? I have excel, but I cannot figure out how to do regression with more than one variable.

Upvotes: 6

Views: 20736

Answers (2)

Mirko Tipka
Mirko Tipka

Reputation: 11

Simple way is to use VBA, not complicated Regression method. You can do it with multiple variables and get multiple results.

Table(sheet1):

x1 is cell A1 in VBA sheet1.cells(1,1)
x2 is cell B1 in VBA sheet1.cells(1,2)
x3 is cell C1 in VBA sheet1.cells(1,3)
z is cell D1 in VBA sheet1.cells(1,4)

Calculations are on 2nd sheet(sheet2);

cell A1 = x1 (variable; input for formulas)
cell A2 = x2 (variable; input for formulas)
cell A3 = x3 (variable; input for formulas)
cell A4 = z1 (result)

Create Button and type code in VBA

Private Sub CommandButton1_Click()

'rowCount is numbers of rows in your table(list1)
for m = 0 to rowCount-1

     'set table data to calculations
     'set x1
     sheet2.Cells(1, 1) = sheet1.Cells(2 + m, 1)
     'set x2
     sheet2.Cells(1, 2) = sheet1.Cells(2 + m, 2)
     'set x3
     sheet2.Cells(1, 3) = sheet1.Cells(2 + m, 3)

     'get z
     sheet1.Cells(2 + m, 4) = sheet2.Cells(1, 4)

next m
End Sub

Upvotes: 0

Louis Rhys
Louis Rhys

Reputation: 35637

You are talking about regression analysis. If the relationship is linear, then it is multiple linear regression (more than one independent variables, one dependent variable, linearrelationship) See the links for further info

Edits: To do this analysis with Excel 2007: You must first enable the Analysis ToolPak in Office logo (top left) > Excel Options > Add-Ins > Manage (dropdown menu: Excel Add-Ins) > Go > Check Analysis ToolPak > Ok

Then you can choose Analysis > Regression in the Data ribbon where you can specify multiple columns as your input range. You can find a guide here about more details to use the regression tools, it's for an older version of excel but the regression tool is the same.

Upvotes: 6

Related Questions