After you have entered, edited, and formatted data, you can perform many powerful functions to summarize data, answer specific questions, and solve problems.
In this section, you'll learn about
using statistical analysis tools
creating frequency distribution tables
performing regression analysis
performing matrix operations
creating scenarios
managing scenarios
creating one-variable What-If tables
creating two-variable What-If tables
using goal seeking
finding optimum solutions for linear and nonlinear problems
For more information about analyzing data, see "Reference: Analyzing data."
Using statistical analysis tools
Quattro Pro lets you perform a number of advanced statistical, numerical, and financial analysis tasks. You can access any of the following statistical analysis tools to guide you through the steps you must perform.
Advanced regression
Amortization schedule
ANOVA: one-way
ANOVA: two-way with Replication
ANOVA: two-way without Replication
Correlation
Covariance
Descriptive statistics
Exponential smoothing
Fourier
F-Test
Histogram
Mortgage refinancing
Moving average
Random number
Rank and percentile
Sampling
t-Test
Z-Test
To use an analysis tool
1 Click Tools Numeric tools Analysis tools.
2 Follow the steps in the Analysis Expert.
Note
Before you use an analysis tool, make sure the input cells you are analyzing are arranged properly and contain numeric data, not strings. As well, the analysis tools have varying restrictions on the contents of the input cells and size of the cell area.
Creating frequency distribution tables
A frequency distribution table indicates the values that a variable can take on and the frequency with which each value occurs. For example, you can create a frequency distribution table to find the number of times that numbers between two and five appear in a given block of cells.
You can supplement the statistical information in your notebook with appropriate spreadsheet functions. For example, you can include these formulas at the end of the weekly sales figures contained in a selection named SALES:
@SUM(SALES) Calculates total sales
@COUNT(SALES) Calculates the number of sales in the column
@AVG(SALES) Calculates the average sales amount
To create a frequency distribution table, you need one or more areas of values to count and bin cells listing the value intervals, or bins, for each count you need. Bin cells contain the range of intervals you want analyzed. The bin must be a single column with a column of blank cells to its right, where the results will be written. Value cells must also be placed in a single column.
To create a frequency distribution table
1 Type values in a bin of ranges on the spreadsheet.
2 Click Tools Numeric tools Frequency.
3 Click the Range picker in the Value cells box, and select the value cells.
4 Click the Range picker in the Bin cells box, and select the bin cells.
The bin cell ranges must be in ascending order.
Notes
Each value in the bin cells represents all values from it down to the previous value. The first value represents any value less than or equal to itself.
The result area is one cell longer than the bin cells, and is displayed to the right of the bin cells. The last cell contains the number of values found that were greater than the final number in the bin.
Tip
To create an XY chart of a frequency distribution, specify the bin cells as the x-axis series and the results as the first series of values.
Performing regression analysis
Regression analysis answers the question "To what extent can one or more independent variables explain and predict a dependent variable?" By performing regression analysis, you can create a regression analysis table showing how sets of independent variables affect a certain set of dependent variables. For example, you can estimate how the number of hours worked affects production, or how advertising expenditures affect sales.
The relationship between dependent and independent variables is a linear estimate. With one independent variable, regression analysis plots a line of best fit, also called a regression line, through a scatter plot of each independent-dependent value pair.
Because regressions assume a linear relationship, the results will be more accurate if the data closely matches a linear model. To help you determine accuracy of results, the regression table includes two standard error estimates.
To perform regression analysis
1 Click Tools Numeric tools Regression.
2 Click the Range picker in the Dependent box, and select the column of dependent data.
3 Click the Range picker in the Independent box, and select the column of independent data.
4 Click the Range picker in the Output box, and select the upper-left cell of the output cells where you want to write the regression information.
If you want to force the y-intercept to zero, enable the Zero option in the Y intercept area.
Notes
The data cells you compare in regression analysis must have an equal number of rows.
Regression tables are not automatically updated.
Performing matrix operations
A matrix is a rectangular array of numbers. Each column in a matrix corresponds to one variable, and each row corresponds to a linear constraint. Quattro Pro lets you multiply two matrices and invert a matrix.
Matrix operations can help you solve sets of linear formulas and equations. For example, suppose you have these four unique equations using variables w, x, y, and z:
1w+1x+2y+3z=10
3w+2x+2y+1z=20
1w+0x+3y+4z=15
1w+1x+0y+1z=6
You can express the coefficients (the numbers multiplying the variables w, x, y, and z) of these equations in this "4 by 4" coefficient matrix:
1 1 2 3
3 2 2 1
1 0 3 4
1 1 0 1
And you can express the results of the equations in this "4 by 1" constant matrix (the first variable is at the top of the list, the second is next, and so on):
10
20
15
6
You can use matrix multiplication and inversion to find the value of each variable in the second matrix that satisfies the constraints in the first matrix.
Matrix multiplication is helpful when you need to multiply the elements of one matrix by the elements of a second and sum their products. For example, matrix multiplication can calculate the cost of several types of building materials across development phases, or it can compute job costs involving different labor categories.
Matrix inversion is the first step in solving sets of linear equations. When you multiply a matrix by its inverse, the resultant matrix is an identity matrix (all 1s and 0s, with only a single diagonal of 1s). You cannot invert a matrix that is mathematically singular. A matrix is singular when its determinant equals zero. You cannot invert a matrix with mathematical redundancies or inconsistencies. You can invert "square" matrices only. A square matrix is one with the same number of rows and columns.
To multiply two matrices
1 Click Tools Numeric tools Multiply matrices.
2 Click the Range picker in the Matrix 1 box, and select the first matrix to multiply.
3 Click the Range picker in the Matrix 2 box, and select the second matrix to multiply.
4 Click the Range picker in the Destination box, and specify the destination cells.
Note
The number of columns in the first matrix must equal the number of rows in the second matrix.
To invert a matrix
1 Click Tools Numeric tools Invert.
2 Click the Range picker in the Source box, and select the matrix cells to invert.
3 Click the Range picker in the Destination box, and select the upper left cell of the cells where you want to write the inverted matrix.
Notes
You can invert matrices of sizes up to 90 rows by 90 columns.
If you specify the same source cells as the destination cells, the inverted matrix overwrites the existing matrix.
Creating scenarios
Quattro Pro lets you create scenarios, scenario groups, or display scenarios. A scenario is a snapshot of a data model which is a set of formulas designed to simulate real-life situations and conditions. Scenarios show changing data or variable values plugged into a model and the values that result. For example, a worst-case scenario shows what you can expect from the least desirable set of variable values; a best-case scenario uses the most desirable values as input. Scenarios are often used to find the optimum solution to a problem.
Before using scenarios, you have to create a data model that includes formulas and supporting data.
To create a scenario
1 Click Tools Scenario New.
2 Click the Range picker in the Changing cells box, and select the changing cells.
3 Click Next.
4 Type a name in the Scenario name box.
5 Type a value for each changing cell in the Changing cells and their values boxes.
6 Click Add scenario.
7 Click Next.
8 Click Show scenario.
9 Click Next.
Note
By default, changing cells are yellow; result cells are green.
Tip
You can click Create report to see a Scenario Summary Report.
To create a scenario group
1 Click Tools Scenario Edit.
2 Click Settings.
3 In the Scenario group settings dialog box, click New.
4 In the New group dialog box, type a name in the New group name box.
To display a scenario
1 Click Tools Scenario Edit.
2 Choose a scenario group from the Scenario groups list box.
3 Choose a scenario from the Scenarios list.
Notes
When you display a scenario, its changing cell and result cell values replace those currently on the screen.
When you highlight a scenario in the list and click Show scenario, the values for that scenario display in the notebook. You can drag the Scenario expert dialog box to a new location to view all scenario cells.
Managing scenarios
Quattro Pro lets you keep track of all the scenarios you create. You can save edits to a scenario or you can rename the scenario group if the focus of the scenarios changes.
You can highlight the changing cells of a scenario to make it easier to keep track of the data that you modify.
When you no longer need a scenario, you can delete it from the scenario group, or you can remove the scenario group altogether.
To save scenario edits
1 Click Tools Scenario Edit.
2 Choose a scenario group from the Scenario groups list box.
3 Choose a scenario from the Scenarios list.
4 Edit the scenario on the spreadsheet.
5 Click Capture.
6 In the Capture scenario as dialog box, type the name of the scenario in the Scenario name box.
Tip
You can save the scenario you edited as a new scenario by typing a new name in the Scenario name box.
To rename a scenario group
1 Click Tools Scenario Edit.
2 Click Settings.
3 In the Scenario group settings dialog box, click Rename.
4 In the Rename group dialog box, type the new name in the New group name box.
To highlight cells
1 Click Tools Scenario Edit.
2 Click Highlight.
3 In the Scenario manager highlight dialog box, open the Changed cells color picker, and click a color.
4 Open the Result cells color picker, and click a color.
Note
By default, changed cells are yellow; result cells are green.
To delete scenarios
1 Click Tools Scenario Edit.
If the scenario is in another scenario group, choose the group name from the Scenario groups list.
2 Choose a name from the Scenarios list.
3 Click Delete.
To delete a scenario group
1 Click Tools Scenario Edit.
2 Click Settings.
3 In the Scenario group settings dialog box, choose a group from the Scenario groups list box.
4 Click Delete.
Creating one-variable What-If tables
What-If tables show the results of substituting a number of values for one or two variables in a formula. Also called sensitivity tables, What-If tables can answer questions such as "What if my company's expenses increase 10%, 20%, 30%? And, what if production increases 10% to 25% at the same time?" What-If tables can also be used to look up values, such as postal charges for different parcel weights.
You can create one-variable What-If by varying a single value that is referenced by one or more What-If formulas. You can create one by setting up a column of figures to use as substitutions, and setting up formulas that reference a blank cell (the input cell). Each formula is calculated using the values in the substitution column, and the column below each formula is filled in with calculated values.
For example, suppose you want to set up a table that shows commissions earned for a range of sales amounts. By varying a single value referenced by a What-If formula, you can calculate the commission employees would receive for three different commission rates: 12%, 15%, and 18%. You can create a column of figures that reflect the sales range you want to show, and enter the formulas to calculate the commissions. Each formula must reference a blank cell. This is the input cell; each substitution value will be put in this cell, one at a time, to create the table. The input cell can be any cell outside the table.
To fill in the table values, Quattro Pro moves down each column, enters each row's substitution value in the input cell, recalculates the formula at the top of the column, and places the result in the cell.
Using a one-variable table with a notebook database
A What-If table can be a helpful supplement to existing database information. You can create a table that uses information in a database without affecting the database itself. For information about creating a notebook database, see "Using databases."
For example, you can determine what your net income might be in the year 2001. Suppose your boss promised you an annual raise of 10%, and the current inflation rate is 6.5%. You created a database projecting yearly gross income (based on a yearly 10% increase), expenses (based on a yearly 6.5% increase), and net income (subtracting expenses from gross income) as shown in the next figure.
You can try substituting other variables (for example, different inflation rates) in cell B9 of this example, but this method is slow. A more efficient way of viewing the options is to use a What-If table to show you all the possibilities at once, as in the next figure. Quattro Pro fills the table by moving down each column, entering each row's substitution value in the input cell, recalculating the formula at the top of the column, and placing the result in the cell.
To create a one-variable What-If table
1 Type a column of values to substitute in one or more formulas.
This will be the left column of the table.
2 Type one or more formulas in the row above the first substitution value, starting in the column to the right.
3 Click Tools Numeric tools What-If tables.
4 Enable the One free variable option.
5 Click the Range picker in the What-If table box, and select the cells containing both formulas and substitution values.
6 Click the Range picker in the Input cell box, and select the blank cell referenced in the formulas.
7 Click Generate.
To use a one-variable table with a notebook database
1 Below a notebook database, type a range of data.
2 Above and to the right of the list, enter the address of the cell that shows the formula which references the input cell.
3 Move right one cell, then enter the cell address where you want to display the results.
4 Click Tools Numeric tools What-If tables.
5 Enable the One free variable option.
6 Click the Range picker in the What-If table box, and select the cells containing both formulas and substitution values.
7 Click the Range picker in the Input cell box, and select the blank cell referenced in the formulas.
8 Click Generate.
Creating two-variable What-If tables
Two-variable What-If tables show the results of varying two values in a What-If formula. Unlike one-variable tables, you can use only one formula. You can create a basic table that uses only the data you supply to calculate values, or you can build a table that refers to data already entered in the notebook database.
For example, suppose you own a health club and want to try a sliding-scale fee schedule. You devise a formula to calculate individual yearly fees:
Family Income - (No. of Dependents * 1000)/100
Now you want to build a lookup table to make it easier to pinpoint fees. The next figure displays a sample What-If table using this formula:
Using two-variable What-If tables with a notebook database
A two-variable What-If table can draw out and analyze information in a notebook database. When you create this kind of table, you can use database @ functions to calculate values.
The following example of an employee notebook database uses a What-If table to show how long people have been employed in each department. A criteria table (F12..G13) is set up for two fields: Dept. and Years. The empty cells below the field headings are the input cells. The first column of the data table (F3..F6) lists each department. The first row (G2..I2) shows a range of years employed. The formula in cell F2 counts the number of employees for each department and employment length that's shown in the criteria table:
@DCOUNT(A3..D16, 0,F12..G13)
To create a two-variable What-If table
1 Type a column of substitution values.
2 Starting in the row above and to the right of the substitution column, type a second column of substitution values.
3 In the top-left cell of the table, type the formula to use to calculate table values.
The formula must refer to two blank cells.
4 Click Tools Numeric tools What-If tables.
5 Enable the Two free variables option.
6 Click the Range picker in the What-If table box, and select the cells containing both formulas and substitution values.
7 Click the Range picker in the Column input cell box, and select the column input cell referenced in the What-If formula.
8 Click the Range picker in the Row input cell box, and select the row input cell referenced in the What-If formula.
9 Click Generate.
To use a two-variable table with a notebook database
1 Click Tools Numeric tools What-If tables.
2 Enable the Two free variables option.
3 Click the Range picker in the What-If table box, and select the cells containing both formulas and substitution values.
4 Click the Range picker in the Column input cell box, and select the column input cell referenced in the What-If formula.
5 Click the Range picker in the Row input cell box, and select the row input cell referenced in the What-If formula.
6 Click Generate.
Using goal seeking
Quattro Pro lets you calculate a formula backward, starting with the result and solving for a variable that produces that result. This is called goal seeking; instead of calculating an optimum solution, you change a single variable to reach the solution you specify.
When you know the result you want, but you aren't sure how to reach it, use goal seeking to help you find the answer.
To perform goal seeking
1 Type a formula into a cell. The formula must reference one variable cell.
2 Click Tools Numeric tools Solve for.
3 Click the Range picker in the Formula cell box, and select the cell containing the formula.
4 In the Target value box, type the result you want from the formula.
5 Click the Range picker in the Variable cell box, and select the cell that can be varied to achieve a particular result.
You can also
Specify how many passes from one to 1000 Quattro Pro can make to solve a formula Type a number in the Max iterations box.
Specify how close Quattro Pro has to be to a particular result Type a number in the Accuracy box.
Finding optimum solutions for linear and nonlinear problems
You can use Quattro Pro to find optimum solutions for linear and nonlinear problems. For example, you can
evaluate formulas with one or more variables
solve sets of linear and nonlinear equations and inequalities
find a minimum or maximum solution, or meet an exact goal
find values that satisfy problem limits, or constraints
If you are solving a set of expressions, you can add them as constraints. Once you define a problem you can save the settings for future use.
Producing an Optimizer report
With Quattro Pro, you can produce an Optimizer report that shows how well the constraints were met and indicates starting and final values for the solution cell; starting and final values for the variable cells; and value, binding, slack, and dual value for the constraints.
Slack is the difference between the constraint constant value, or Right Value, and the value actually used in the solution.
Variable dual values are sometimes called reduced gradients. They show how a one-unit increase in the variable affects the solution cell.
Constraint dual values, or Lagrange Multipliers, measure the amount by which the solution could be improved if the constraint were relaxed by one unit.
Increment for variable cells is the amount of increase in the gradient value before a different value is calculated for that variable cell in the solution. For constraint cells, increment is the amount of increase in the right value before the binding status changes.
Decrement for variable cells is the amount of decrease in the gradient value before a different value is calculated for that variable cell in the solution. For constraint cells, decrement is the amount of decrease in the right value before the binding status changes.
To solve a problem
1 Type a single formula into a cell.
The formula must reference any variable cells you have assigned.
2 Click Tools Numeric tools Optimizer.
If you have a goal-seeking problem with one formula, click the Range picker in the Solution cell box, and select the cells that contain the formula.
3 Enable one of the following options:
None-chooses a default solution cell
Max-maximizes the formula
Min-minimizes the formula
Target value-makes the solution cell formula evaluate to an exact value
4 Click the Range picker in the Variable cell(s) box, and select the variable cells.
Notes
If you are solving a set of expressions, enter their left terms into cells. You can enter the right term into adjacent cells, or you can enter the value into the Optimizer directly when adding constraints.
You can reference up to 200 variable cells.
Variable cells must not contain dates, formulas, or text, and they must not be protected.
To specify the Optimizer options
1 Click Tools Numeric tools Optimizer.
2 Click Options.
3 In the Optimizer options dialog box, modify any options.
Note
For more information on Optimizer options, see "Specifying the Optimizer tool options."
To add an Optimizer constraint
1 Click Tools Numeric tools Optimizer.
2 Click Add.
3 In the Add constraints dialog box, click the Range picker in the Cell box, and select a cell containing a formula that must be bounded, or that must equal a target value. You can select a single cell, or select cells that must all have the same bound.
4 Enable the operator option that connects both terms of the constraint and describes their relationship.
5 Click the Range picker in the Constant box, and select a cell that contains a value, or type a value for the target or bound.
Note
You can specify a selection of cells equal to or larger than the number of cells included on the left side of the constraint. Each cell on the left is compared with the corresponding cell on the right. A single cell on the left is compared with the upper left cell of the cells on the right.
Tips
To edit a constraint, select it in the Optimizer dialog box, and click Change.
To delete a constraint, select it in the Optimizer dialog box, and click Delete.
To save an Optimizer model
1 Click Tools Numeric tools Optimizer.
2 Click Options.
3 In the Optimizer options dialog box, click Save model.
4 In the Save optimizer model dialog box, click the Range picker in the Model cells box, and select the cells to hold the model.
Notes
The cells must be three columns wide, with enough rows to hold the model. You can select the cell in the upper left corner of the cells.
The last Optimizer problem you defined is always saved with the note.
To reuse an Optimizer model
1 Click Tools Numeric tools Optimizer.
2 Click Options.
3 In the Optimizer options dialog box, click Load model.
4 In the Load optimizer model dialog box, click the Range picker in the Model cells box, and select the cells that contain the model.
To produce an Optimizer report
1 Click Tools Numeric tools Optimizer.
2 Click Options.
3 In the Optimizer options dialog box, click Reporting.
4 In the Report output cells dialog box, click the Range picker in the Answer report cells box, and select the cells.
5 Click the Range picker in the Detail report cells box, and select the cells.
6 Click OK.
7 Click OK.
8 Click Solve.
Reference: Analyzing data
This section contains additional information related to analyzing data. For example, it contains an in-depth explanation about interpreting an amortization table, and a mortgage refinancing table.
You will also learn about understanding the regression output cells and about the different options available with the Optimizer.
Interpreting an amortization table
You can generate an amortization schedule, showing principal and interest payments, as well as interest paid from start to date and from year to date.
The amortization table consists of two sections. In the top section you can input values related to your mortgage. You can change any of these input values to customize the bottom section of the table.
Column name Description
Interest Rate The annual interest rate of the loan
Term Number of years of the loan, not the number of payments
Original Balance Money originally borrowed
Ending Balance The amount of principal remaining to pay off at the end of the loan
Last Year The number of years to show in the table
1st PMT The first payment date for the mortgage
The bottom section of the table consists of 10 columns:
Column name Description
Pmt # Number of payments from the first payment to the last payment of the loan or to the last one in the last year specified by Last Year
Date Date on which the corresponding payment number is due. Mortgage payments are typically due on the first day of the month following the month covered by the payment. The default date format is Month-Year (MMM-YY).
Yr Rate Equivalent yearly interest rate for the period; for fixed-rate loans, this rate does not change; for variable rate loans, specify an estimated average interest rate when you create the table, then replace individual monthly rate values later by overwriting the calculated values.
P&I Payment Shows the principal and interest payment for the corresponding payment period. This will not vary for fixed loans, but will vary with variable interest loans or other variable loans such as graduated payment loans. In the case of "graduated payment" loans, you can type in the actual P&I payment to replace the calculated value. Even if this results in a negative amortization, the New Balance and other columns calculate correctly. Negative amortization occurs when the P&I payment is insufficient to cover the interest charged for the period, and so the loan balance increases by the shortage amount.
Principal Principal portion of the P&I payment
Interest Interest portion of the P&I payment
Extra Prin Amount of extra principal per pay period. If you intend to pay extra principal for some portion of the loan, enter the amount in this column at the row of the corresponding payment number. The extra principal amount is included in every row that follows. If extra principal payments vary from month to month, type in the actual extra principal amount wherever it changes.
New Balance Balance after each payment. The balance decreases with each payment and eventually reaches zero (or Ending Balance, if it is not zero). The amortization table stops short of zero if Last Year precedes the end of the loan.
Cum. Interest Cumulative interest paid through the corresponding payment
Yearly Total Int Cumulative interest paid for each calendar year. Use this column to calculate the yearly interest paid for tax purposes.
Interpreting a mortgage refinancing table
The mortgage refinancing table provides you with information about refinancing a mortgage and lets you compare your current loan with candidate loans. The mortgage refinancing table consists of two sections: the Current Loan section and the Candidate Loans section.
The Current Loan section takes information about your present mortgage from the Analysis Experts dialog box.
Column name Description
Rem. Term The number of years remaining on the loan, not the number of payments
Balance The remaining principal to pay off in the current loan
Rate The annual interest rate of the current loan
Candidate Loan Rate The annual interest rate of the proposed loan
Fees The amount of percentage fees for the proposed loan
The Candidate Loans section takes information about the rates and terms of different mortgages from the Analysis Experts dialog box. The table calculates the gross savings by showing how different the monthly payment is for the candidate loan compared to the current loan. It also calculates the net savings, by determining the amount of money saved.
The Candidate Loans section of the table consists of 19 columns:
Column name Description
Loan Description Name of the candidate loan
Current Balance Balance of the current loan
Cash Out Cash removed from equity (a positive value), or cash paid up front into the loan to pay down the balance or to prevent financing or loan fees (a negative value)
Pct Fees Percentage fees (points) charged by the lender (for example, 1.75%)
Fixed Fees Fixed fees involved in the refinancing; if a loss (for example, a loan prepayment penalty), enter a positive value; if a gain (for example, a prorated return of prepaid Private Mortgage Insurance from payoff of current loan), enter a negative number
Loan Amount Amount of the candidate loan
Rate Annual interest rate of the candidate loan
Term (yrs) Loan term in years
Future Value Future value of the candidate loan; enter a positive value if the loan requires a balloon payment
P&I Payment Principal and interest payment for each period
Savings: Gross The difference in the monthly payment of the candidate loan and the current loan; if negative, the candidate's monthly payment is higher; if positive, the candidate's monthly payment is lower and all gross savings are applied as extra principal
Savings: Term +/- Portion of gross savings due to lengthening the term of the loan; if the term is lengthened, Term +/- is negative; if the term is shortened, Term +/- is positive
Savings: Cash I/O Portion of gross savings due to closing costs paid up front (a negative number), or the amount of gross savings that has already been reduced by cash taken out (a positive number enclosed by "><", which does not affect the Net amount)
Savings: Net Sum of savings from Gross, Term +/-, and Cash I/O
Payback: When The initial estimate of the number of years it will take for the candidate loan to pay for the cost of refinancing; displays "Never" if the refinance loses money
Payback: Loan Life The number of years you plan to keep the property before selling it or refinancing
Payback: Bal chg The difference in the balance at the end of Loan Life of the candidate loan and the current loan due to extra principal paid on the candidate loan; a negative number represents money saved
Understanding regression output cells
The regression output is nine rows deep and three columns wider than the number of columns in the independent cells. Make sure to leave enough blank space because any underlying data will be overwritten. The output cells contain the following information:
Output Cell Description
Constant The y-intercept value, zero if Y Intercept is set to Zero instead of Compute
Std Err of Y Est The estimated standard error of y values; the degree of deviation of observed y values from predicted values
R Squared The variance; the degree of relationship between independent and dependent variables. With one independent variable, R Squared is the square of the correlation between the two variables.
No. of Observations The number of values for each variable; the number of rows in the regression table
Degrees of Freedom The number of observations minus the number of independent values being computed by the regression. With Y Intercept set to Zero, Degrees of Freedom = (No. of Observations) - (number of independent variables); with Y Intercept computed, Degrees of Freedom = (No. of Observations) - (number of independent variables + 1).
X Coefficient(s) The regression coefficients of the independent (x) variables; the slope of the regression line representing the relationship between each independent variable and the dependent variable.
Std Err of Coef. An error estimate of the X Coefficient above it. Interpret each coefficient as the X Coefficient value plus or minus the Standard Error of Coefficient.
Specifying the Optimizer tool options
When you produce an optimizer report, you can specify the following options:
Assume linear model (use Simplex Method)-lets you use a linear method to solve the problem by indicating that the problem has only linear expressions in the solution cell or any of the constraint cells
Show iteration results-lets you pause between each iteration, or trial, to check the progress of the search. You can either stop or continue the search after each iteration.
Automatic scaling of variables-lets you scale variables to achieve the target value
Maximum time (in seconds)-lets you indicate the maximum amount of time to find the best solution to the problem
Maximum number of iterations-lets you set the maximum number of iterations, or trials, that Optimizer can perform to find the best solution
Allowed solution precision-lets you control the accuracy of the solution
Allowed solution tolerance-lets you indicate the maximum percentage by which a solution can differ from a theoretical optimum integer solution. As you increase the tolerance setting, solutions are produced more quickly; however, they may be less accurate.
Estimates-lets you select the approach for obtaining initial estimates of the basic variables in each iteration
Derivatives-lets you select the Forward or Central differencing for estimates of partial derivatives
Search-lets you select a method for computing the search direction
