CB.BatchFitND

Subtopics

The Batch Fit tool helps you define assumptions when you have historical data for a number of variables. The tool automatically selects which probability distribution (binomial, normal, triangular, uniform, and so on) best fits each series of historical data and provides you with the parameters to use in your model. Batch Fit can also provide a table of goodness-of-fit statistics and a correlation matrix calculated between the historical data.

This call runs the Batch Fit tool and displays results without using a dialog.

Note:

Before calling CB.BatchFitND, reset the Crystal Ball simulation.

Table 9. CB.BatchFitND Parameters

Parameter	Required?	VBA Data Type	Table
Index	Required	Integer	Table 10
Value1	Optional	Variant	Table 11
Value2	Optional	Variant	Table 12
Value3	Optional	Variant	Table 13
Value4	Optional	Variant

Table 10. CB.BatchFitND Index Parameter — Required, Integer

Named Constant Value	Index Value	Description
cbBftInputRange	1	Used with Value1 to select the location of the data series for fitting, a range address string. The range must be contiguous (adjoining cells)
cbBftInputOrientation	2	Used with Value1 to specify whether the data cells are in rows or columns
cbBftInputHeader	3	Used with Value1 to indicate whether the input range has headers. If set to True, there is header text in the top row or first column, depending on the cbBftInputOrientation setting.
cbBftInputLabel	4	Used with Value1 to indicate whether the input range has labels. If set to True, there is label text in the first column or top row, depending on the cbBftInputOrientation setting.
cbBftSelectDist	5	Used with Value1 to choose a specific distribution to fit; takes cbDfa[distribution constant] as specified in Table 76
cbBftSelectDistAll	6	Chooses all available continuous and discrete distributions to fit
cbBftSelectDistClear	7	Clears all distributions currently selected for fitting
cbBftSelectDistAllContinuous	8	Chooses all available continuous distributions to fit
cbBftSelectDistAllDiscrete	9	Chooses all available discrete distributions to fit
cbBftSelectDistAuto	10	Automatically chooses which distributions to fit
cbBftRankMethod	11	Used with Value1 to choose a ranking method, as listed in Table 78
cbBftOutputLocation	12	Used with Value1 to specify an output location in a new workbook
cbBftOutputFormat	13	Used with Value1 to specify whether output is formatted. If set to True, it is formatted.
cbBftOutputCorrelationMatrix	14	Used with Value1 to specify whether to output a matrix of correlations between the data series. If set to True, a matrix is produced.
cbBftOutputCorrelation	15	Used with Value1 to specify whether to correlate assumptions
cbBftLinkToCorrelationMatrix	16	Used with Value1 to specify whether to link to the matrix of correlations between the data series, assuming cbBftOutputCorrelationMatrix [14] is set to True. If set to True, creates a link to the matrix.
cbBftOutputGoodnessOfFitReport	17	Used with Value1 to specify whether to output a goodness-of-fit report. If set to True, a report is produced. Value2 can be used to supply a sheetname as a string.
cbBftOutputAssumReport	18	Used with Value1 to specify whether to output an assumptions report. If set to True, a report is produced. Value2 can be used to supply a sheetname as a string.
cbBftOutputAssumFullStatistics	19	Used with Value1 to specify whether to include full statistics in assumptions reports. If set to True, full statistics and percentiles (deciles) are included. Otherwise, only parameter values and assumption charts appear.
cbBftRun	20	Runs the Batch Fit tool
cbBftUseDistLocking	21	Sets the Batch Fit tool to use parameter locking, the same as checking Lock Parameters on the Fitting Options panel of the Batch Fit wizard
cbBftOutputGoodnessOfFitAllStats	22	Used with Value1 to indicate that all statistics should appear when a goodness-of-fit report is produced. When set to True, this is the same as checking Show All Goodness-of-Fit statistics on the Output Options panel of the Batch Fit wizard.
cbBftFitDistLockParam	23	Used with Value1, Value2, Value3, and Value4 to specify which parameter to lock and which value to use
cbBftFitDistLockParamClear	24	Used with Value1 and Value2 to specify which parameter to clear
cbBftOutputOrientation	30	Used with Value1 to specify whether output is in rows or columns
cbBftOutputSheetName	31	Specifies the name of the worksheet containing primary fit results (assumptions) as the string given for Value1

Table 11. CB.BatchFitND Value1 Parameter — Optional, Variant

Related Value	Named Constant Value	VBA Data Type	Index Value	Description
For Index = 1, cbBftInputRange	A cell reference with format: Range("A1:C300") or "A1:C300"	Variant	n/a	Specifies the range of data to use for fitting
For Index = 2, cbBftInputOrientation	cbBftInputRows	Integer	1	Indicates that data cells are in rows
For Index = 2, cbBftInputOrientation	cbBftInputColumns	Integer	2	Indicates that data cells are in columns
For Index = 3, cbBftInputHeader	True or False	Boolean	n/a	Indicates whether the data range has headers. If set to True, there is header text in the top row or first column, depending on the cbBftInputOrientation setting.
For Index = 4, cbBftInputLabel	True or False	Boolean	n/a	Indicates whether the data range has labels. If set to True, there is label text in the first column or top row, depending on the cbBftInputOrientation setting.
For Index = 5, cbBftSelectDist	cbDfa... from Table 77	Integer	0-23, 50-53	Indicates a specific distribution or distribution type(s) to fit to the data
For Index = 11, cbBftRankMethod	Methods listed in Table 78	Integer	1-5	Specifies a ranking method, as listed in Table 78
For Index = 12, cbBftOutputLocation	cbBftNewWorkbook	Integer	1	Used with Value2 to indicate whether results should go in a new workbook
For Index = 12, cbBftOutputLocation	cbBftNewWorksheet	Integer	3	Used with Value2 to indicate whether results should go in a new worksheet
For Index = 13, cbBftOutputFormat	True or False	Boolean	n/a	Indicates whether results should be formatted; if True, formatting is applied
For Index = 14, cbBftOutputCorrelationMatrix	True or False	Boolean	n/a	Indicates whether to create a correlation matrix; if True, the matrix is produced
For Index = 15, cbBftOutputCorrelation	True or False	Boolean	n/a	Indicates whether to correlate assumptions; if True, assumptions are correlated. Value2 can be used to indicate a correlation threshold.
For Index = 16, cbBftLinkToCorrelationMatrix	True or False	Boolean	n/a	Indicates whether to link to the matrix of correlations between the data series; if True, creates the link
For Index = 17, cbBftOutputGoodness OfFitReport	True or False	Boolean	n/a	Indicates whether to output a goodness-of-fit report; if True, the report is produced
For Index = 18, cbBftOutputAssumReport	True or False	Boolean	n/a	Indicates whether to output an assumption report; if True, the report is produced
For Index = 19, cbBftOutputAssumFullStatistics	True or False	Boolean	n/a	Indicates whether to include full statistics with an assumption report; if True, the report contains full statistics, including deciles
For Index = 21, cbBftUseDistLocking	True or False	Boolean	n/a	Indicates whether the Batch Fit tool should use parameter locking; if True, parameter locking is set (the same as checking Lock Parameters on the Fitting Options panel of the Batch Fit wizard)
For Index = 22, cbBftOutputGoodnessOf FitAllStats	True or False	Boolean	n/a	Indicates whether all statistics should appear when a Goodness of Fit report is produced, the same as checking Show All Goodness-of-Fit statistics on the Output Options panel of the wizard; if set to True, all statistics appear
For Index = 23, cbBftFitDistLockParam	The name of a lockable distribution from Table 53; see Description	Integer	The index number of a lockable distribution from Table 53; see Description	Specifies one of the following distributions whose parameters are locked. The following distributions from Table 53 can be used as Value1 parameters: Gamma distribution — Location, Shape parameters Lognormal distribution — Location parameter Student's t distribution — Degrees of Freedom parameter Weibull distribution — Location, Shape parameters Binomial distribution — Trials parameter Hypergeometric distribution — Trials parameter Use Value2 to specify the number of the target parameter.
For Index = 24, cbBftFitDistLockParamClear	The name of a lockable distribution from Table 53; see Description	Integer	The index number of a lockable distribution from Table 53; see Description	Specifies one of the following distributions whose parameters are cleared. The following distributions from Table 53 can be used as Value1 parameters: Gamma distribution — Location, Shape parameters Lognormal distribution — Location parameter Student's t distribution — Degrees of Freedom parameter Weibull distribution — Location, Shape parameters Binomial distribution — Trials parameter Hypergeometric distribution — Trials parameter Use Value2 to specify the number of the target parameter.
For Index = 30, cbBftOutputOrientation	cbBftOutputRows	Integer	1	Indicates that output cells are in rows; equivalent of selecting Fill to the right in the user interface
For Index = 30, cbBftOutputOrientation	cbBftOutputColumns	Integer	2	Indicates that output cells are in columns; equivalent of selecting Fill downward in the user interface

Table 12. CB.BatchFitND Value2 Parameter — Optional, Variant

Related Value	Named Constant Value	VBA Data Type	Index Value	Description
For Index = 12, cbBftOutputLocation	True or False	Boolean	n/a	Used with Value1 = cbBftNewWorkbook or cbBftNewWorksheet to indicate whether the output goes in a new workbook or worksheet. If cbBftNewWorksheet is set to False, Value3 can be used to indicate the first (upper left) cell of the output range on the current worksheet. Otherwise, cell A1 is assumed.
For Index = 15, cbBftOutputCorrelation	A whole number or decimal between 0 and 1, inclusive	Double	n/a	Used with Value1 = True to indicate that assumptions should be correlated if the absolute value of their correlation is equal to or greater than the specified threshold
For Index = 17, cbBftOutputGoodnessOfFitReport	A string that indicates the report name	String	n/a	Used with Value1 = True to specify a report name string
For Index = 18, cbBftOutputAssumReport	A string that indicates the report name	String	n/a	Used with Value1 = True to specify a report name string
For Index = 23, cbBftFitDistLockParam	The index of a lockable parameter from Table 53; see Description	Integer	The index number of a lockable parameter for the distribution specified in Value1 (all from Table 53; see Description)	Specifies one of the following distribution parameters to lock. The following from Table 53 can be used as Value2 parameters: Gamma distribution — Location, Shape parameters Lognormal distribution — Location parameter Student's t distribution — Degrees of Freedom parameter Weibull distribution — Location, Shape parameters Binomial distribution — Trials parameter Hypergeometric distribution — Trials parameter Use Value3 to specify the value of the target parameter.
For Index = 24, cbBftFitDistLockParamClear	The index of a lockable parameter from Table 53; see Description	Integer	The index number of a lockable parameter for the distribution specified in Value1 (all from Table 53; see Description)	Specifies one of the following distribution parameter(s) to clear. The locking settings and values are cleared. The following from Table 53 can be used as Value2 parameters: Gamma distribution — Location, Shape parameters Lognormal distribution — Location parameter Student's t distribution — Degrees of Freedom parameter Weibull distribution — Location, Shape parameters Binomial distribution — Trials parameter Hypergeometric distribution — Trials parameter

Table 13. CB.BatchFitND Value3 Parameter — Optional, Variant

Related Value	Named Constant Value	VBA Data Type	Value	Description
For Index = 23, cbBftFitDistLockParam	n/a	Double	A number within the acceptable value range for the specified parameter according to Table 53	The actual value to be used for the specified parameter. Cell references cannot be used here.

Note:

Before you call CB.BatchFitND, reset the Crystal Ball simulation.