Objective Grid functions are predefined formulas supplied with the program that perform the work of many formulas or perform special functions that cannot be achieved by formulas, such as manipulating text strings. They offer a shortcut approach to accomplishing the work of long, complex formulas. Mathematical and statistical functions are often used to sum a column of numbers, compute an average, determine a minimum or maximum value, or round the results of a formula. Other functions are used for more specialized purposes such as computing the future value of an investment or the product of multiplying one cell range by another range. Some functions perform calculations that arithmetic operators cannot handle such as text-string manipulations.

An argument is a parameter appended to a function statement, specifying the values that Objective Grid should use in calculating the function. In the syntax statement for a function, the argument list is the list of arguments the function should use for its calculation.

Objective Grid functions fall into the following categories:

Mathematical functions perform calculations such as determining absolute value, finding the integer portion of a number, or establishing the value of a constant. Although you could accomplish these tasks with a formula, using a function saves time and trouble.

Objective Grid also provides a full range of trigonometric functions including sine, cosine, tangent, arc sine, hyperbolic sine, hyperbolic arc sine, as well as vector and matrix arithmetic and manipulation.

Mathematical functions perform calculations with numeric values as arguments, returning numeric values.

Statistical functions perform aggregation operations such as calculating means, minimums, maximums, and averages.

Objective Grid also provides more sophisticated statistical test functions that perform operations on a group of values expressed as a list of arguments. These include the F-test, t-tests, correlation coefficient, deviations, and all common averages.

Statistical functions return numeric values.

Conditional statistical functions operate much like statistical aggregation functions, except that the last argument is a constraint expression that Objective Grid evaluates for each cell in the argument list. Only cells that meet constraint criteria are included in the calculation. The constraint expression may be any Objective Grid expression that evaluates to a numeric result.

Conditional statistical functions return a numeric value.

String functions manipulate and evaluate character strings. For example, string functions can return the length of a string, find the first occurrence of a string in a range, change a string from uppercase to lowercase and vice versa, or replace one string with another.

String functions return strings or numeric values.

Logic functions return one value if an argument meets certain criteria, another value if it does not. Logic functions are used as an adjunct to conditional statements.

Logic functions return the value 1, 0, or a value.

Digital logic functions perform digital logic operations such as AND, OR, NOT, etc.

Digital logic functions return the values 0, 1, or -1 (unknown). Any value whose integer portion is not equal to 0 or 1 is considered unknown. Unknown input values may cause unknown output values.

Financial functions perform common financial calculations, such as calculating the future value of an annuity at a given interest rate, straight-line depreciation, double-declining depreciation, or the payment term for a given investment. The financial functions in Objective Grid cover annuities, cash flows, assets, bonds, and Treasury Bills.

Financial functions are most useful for solving cash flow calculations where you know all but one variable. For example, if you know the present value of an investment, interest rate, and periodic payment, you can use the @FV function to calculate the future value of the investment. If you know the future value and other variables, but need to know the present value, you can use the @PV function.

Many financial functions require specifying a Day Count Basis. A Day Count Basis indicates the way in which the days in a month and the days in a year are to be counted. Most of the financial functions in securities involve four different Day Count Bases: 30/360, actual/actual, actual/360, and actual/365.

30/360 Day Count Basis assumes 30-day months and 360-day years (12 months x 30 days). Objective Grid also follows the "End-of-Month" rule which assumes that a security pays interest on the last day of the month and will always make its interest on the last day of the month. Special rules are followed when calculating the days between two dates on 30/360 Day Count Basis.

For example, let Start_Date = D1/M1/Y1, End_Date = D2/M2/Y2.

The special arguments used by Objective Grid financial functions are defined in Table 20.

Functions related to fixed income securities usually require special dates as arguments: issue date, settlement date, first coupon date, last coupon date, maturity date of a security. When specified, the following constraints should be followed:

Date and time functions return values corresponding to the specified date, month, year, hour, minute, or second. You can also use date/time functions to enter the current system time and date in a cell. These functions open up many possibilities for managing accounts receivable and calculating test times.

Objective Grid internally stores date and time information using the same convention as other popular spreadsheet programs:

Using this convention, date and time values may be used together. For example, the date/time value 1.25 corresponds to 6:00:00 AM, January 1, 1900.

Miscellaneous functions perform a variety of calculations, such as returning a reference to specific cells or ranges or returning the Nth argument from a list of arguments.

Embedded tools are a set of functions in Objective Grid that have the ability to return data in a matrix, not just the resident cell. These functions make non-scalar operations such as matrix multiplication and "live" recalculation as easy to use as an ordinary spreadsheet function.

Embedded tools are a powerful feature in Objective Grid. Their power derives in part from their ability to return a set of data, not just a single value. This function makes non-scalar operations such as matrix multiplication and "live" recalculation as easy to use as an ordinary spreadsheet function.

Embedded tools store values in a group of adjacent cells. These adjacent cells are set to constant formulas, with explicit dependencies on their neighboring cells. For example, an embedded tool in cell B2 might produce the formula =1.3459/B2 in cell B3. This formula indicates that the cell currently contains the constant 1.3459 but that its value depends on the contents of cell B2 (the cell containing the embedded tool).

This notion of explicit dependencies is important for recalculation. It guarantees that any cell that references B3 will not be recalculated until after cell B2 is recalculated. This ensures that data generated by the embedded tool is always current.

Embedded tools look like normal functions, and they can be copied, moved and formatted just as any other formula in the spreadsheet. However, you must follow one important guideline: DO NOT combine embedded tools with other embedded tools in a single formula. For example, the formula @INVERT(@MMUL(A1..C4,F1..I3)) is not allowed.

You enter a function in a cell in the same way you enter a formula or any other entry, with a few additional guidelines.

Arguments specify the values the function should use in its calculations. The number of arguments, their types, and their formats varies from one function to another. Arguments are usually numeric values, cell or range references, or string values. Most functions have at least one argument; a few have none.

The following table shows different types of arguments used in Objective Grid functions.

The result of a function depends on the order in which Objective Grid handles the calculations. Please see “Calculations” for more information on operators and their precedence.

Computed cell references are the result of a function that is itself a cell reference or range reference.

Several Objective Grid functions such as @CELLREF and @RANGEREF return a result that is itself a cell reference or range reference. This is a powerful facility, but it must be used with caution because Objective Grid cannot take these indirect references into account when determining the order of recalculation. The same caution applies to constraint expressions used in conditional statistical functions. As a rule, cells that are indirectly referenced by a function are not automatically recalculated. Objective Grid provides a special construct to force a recalculation, referred to as an explicit dependency.

Objective Grid does not recalculate the spreadsheet unless explicit dependencies have been changed, so you may need to force recalculation if you change the value of a cell that is referenced only indirectly through a function.

For example, suppose you want to count the numeric values in the range C3..J100 that fall within the limits specified in cells A1 and A2. The Objective Grid formula to compute this is @CCOUNT(C3..J100,#A1 && #<A2).

This formula will correctly count the numeric values in the range C3..J100. However, if you change the value in A1, Objective Grid will not automatically recalculate the result, because A1 is referenced only indirectly through the constraint expression.