Clicking on a function shows its arguments. We describe the functions and arguments below. We have not provided Help files within Excel. The functions in this section are listed with the add-in that uses them. Of course, they will have different prefixes for those add-ins. Examples of their application can be found in the source code and demonstration files of the associated add-ins. Usually add-ins place the function directly on the worksheet as a particular data form is constructed. Most can also be placed directly on a worksheet by the user. Not all functions are included in paj_components, only those which could be easily extracted and those that might have some general usefulness.

Range argument refer to a range on a worksheet. Excel identifies a range by two cell designations separated by a colon such as A1:C10, or by a name. Names are defined by add-ins or inserted by the INSERT/NAME command.

Certain functions have been found to be useful in a variety of add-ins.

This is a boolean function that returns TRUE if there is a named range in the workbook with the name range_name. Otherwise it returns FALSE.

We provide moving average and moving regression functions below. The Forecast add-in has a variety of additional functions that can be extracted. Unless otherwise indicated a range argument must be either a single row or single column.

This function computes the moving average. It has two arguments: a range and the number of values to be used for the moving average, or the moving average interval. The number of elements in the range must be at least as great than the moving average interval. The function is useful because it allows experimentation over different values of the moving average interval. Only numeric values in the range are used in the average with blanks or strings contributing to neither the numerator or denominator of the average. At least one cell in the averaged range must contain a number. Otherwise the function returns "***".

This function is used to compute the constant value of a linear regression equation. The range argument contains the dependent values used to fit the regression line. Only a specified number of elements in the range are used. This regression interval is given as the second argument. The variable regression interval is useful for forecasting because it allows experimentation over different values. The function is used for regression forecasting. Only numeric values in the range are used with blanks or strings not contributing to the result. At least two cells in the range must contain a number. Otherwise the function returns "***".

This function is used to compute the trend value of a linear regression equation. The arguments are the same as used for computing the constant term.

This function is used to make forecasts with linear models. The first two arguments are the ranges of the constant and trend estimates respectively. The third argument is the time interval for the forecast. The function retrieves the values of A and B computed t periods earlier and computes.

A + Bt

The function returns "***" if any of its arguments are not numeric.

This function computes the mean absolute deviation of the numbers in a range. The absolute value of each number in the range is computed and the average of these values is returned as the result. This is an interesting measure for forecasting errors because it is increased by the absolute value of both the mean and variation about the mean of the errors.

This function returns a random number drawn from a uniform distribution with range 0 to 1. The function uses the internal Excel random number generator. The value of the function depends on the seed given by the argument RNseed. We use this function in the forecast simulations by having the random number for one period be the seed for the next. Then by specifying the first seed, the complete sequence of random values is determined. This is handy so the same sequence of random numbers can be used in different simulation experiments.

This function is used to simulate an observation from a Normal distribution with mean and standard deviation given by the arguments mu and sigma respectively. The random_number argument is a number between 0 and 1 and is usually a provided by a random number generator.

This function is does the same as the Excel SUMPRODUCT function except on different size ranges. Elements of the two ranges are in range1 and range2. The numbers in range 1 to be summed are in columns that differ by interval1. The numbers in range2 which are to be multiplied by the numbers in range1 are in columns that differ by interval2. The arguments start1 and start2 indicate which elements are to be summed. Examples are below when range1 = (1, 2, 3, 4, 5, 6) and range2 = (1, 2, 3, 4)

FF_SUMPRODUCT((1, 2, 3, 4, 5, 6),(1, 2, 3, 4),3, 2, 1, 1) = 1*1 + 4*3 = 14

FF_SUMPRODUCT((1, 2, 3, 4, 5, 6),(1, 2, 3, 4),3, 2, 2, 2) =2*2 + 5*4 = 24

FF_SUMPRODUCT((1, 2, 3, 4, 5, 6),(1, 2, 3, 4),3, 2, 3, 1) = 3*1 + 6*3 = 21

The Inventory add-in provides a large number of functions directly related to inventory theory. Two that have general applicability compute the greatest common divisor and greatest common multiple.