Geek Answers Handbook

How to calculate a formula with multiple variables from a data table

I have a table of several independent variables that I need to calculate a formula to generate a dependent variable. Despite the trial version, I got the 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 need a formula such that:

 f(x1,x2,x3) = z

Now, how do I get through and get the value? Can you tell me some resources?

It seems to me that I should remember this from school days, but I do not know.

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

+6
statistics
source share
3 answers

You say regression analysis . If the relation is linear, then this is multiple linear regression (more than one independent variable, one dependent variable, linear dependence). See links for further information.

Editing:. To do this analysis with Excel 2007: first you need to enable the Analysis ToolPak logo in Office (top left)> Excel Options> Add-Ins> Control (drop-down menu: Excel Add-Ins)> Go> Check Analysis ToolPak> Ok

Then you can choose Analyze> Regression in the Data ribbon, where you can specify multiple columns as the input range. You can find the manual here in more detail to use the regression tools, this is for an older version of excel, but the regression tool is the same.

+6
source share

A simple way is to use VBA, not a complicated regression method. You can do this with several 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)

Settlements on the 2nd sheet (sheet 2);

 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 a button and enter the 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
0
source share

Can anyone help me solve this problem?

We have a form sheet with 45 actifs members (identified by the Bidder code in column C). On the plate, we send out announcements to our participants (announcement number in column B). Ads have various variables: - they are sent by the seller (his code in column D) - category - brand - they are available or not - with VAT or not - they have a new price - they have the best offer price - the seller asked for a return price (or no) - the amount of the counter purchase (if any) - the commission paid for the transaction - the year of the car - km

Result (dependent variables):

  • bet is made (if the amount in R> 0)
  • bid selected (yes or no)
  • best bet (columnn P)
  • price range (column S)

For missing parts: -99999

A few more details:

All announcements are sent to everyone (45 bidders / bidders). They choose whether they will send a bid or not. Adding with a participant code of -99999 in column C means that no one sent a bit.

Actual observations: 743 announcements x 45 participants = 33,435, but I did not include unsent applications (bids in R columns are 0).

My questions:

  • I would like to be able to predict the likelihood that certain bidders will respond to the announcement (amount in column R) ... based on the seller’s variables / categories / brand / shares / VAT / price / counter bid / commission / year / km, aggregate ads

  • And the likelihood that he will make the best bet on the ad (1 in column P) .... based on the same variables, based on bets made

Do you know how to analyze data?

0
source share

More articles:


All Articles