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 function—Performs calculations with numeric values as arguments.
Statistical function—Performs aggregation and counting operations on a group of values expressed as a list of arguments.
Conditional Statistical function—Operates 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.
String functions—Act on strings of text, rather than on numeric values.
Logical function—Evaluates conditions on a purely true/false basis, returning the value 0 if the condition is False and the value 1 if the condition is True.
Digital Logical function—Returns the values 0, 1 or -1 (unknown) based on the value of its arguments. Digital logical functions evaluate the integer portion of a value. Any value not equal to 0 or 1 is considered unknown.
Financial function—Performs a common financial calculation, such as future value of an annuity at a given interest rate.
Date and Time function—Returns a value corresponding to the specified date, month, year, hour, minute or second. It can also be the current system time and date.
Miscellaneous function—Performs a variety of calculations such as returning a reference to a specific cell or range.
Embedded Tools—Have the ability to return data in a matrix, not just the resident cell.
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.
If D1=31, Objective Grid uses 30 for D1.
If D2=31, Objective Grid uses 31, unless D1=30 or D1=31. In this case, Objective Grid uses 30.
If D1 is the last day of February (D1=28 or 29 in a leap year), Objective Grid uses 30 for D1.
If D2 is the last day of February (D2=28 or 29 in a leap year) and D1 is also the last day of February, Objective Grid uses 30 for D2.
The special arguments used by Objective Grid financial functions are defined in Table 20.
| Function | Purpose |
| interest rate | The interest rate to be used in the calculations. The rate may be specified as annual, monthly, or quarterly, but it must agree with the increment you use for periods. By default, the interest rate is an annual rate. |
| present value | The present value of an investment, representing the amount already received from or committed to an investment. |
| period | The number of periods over which the loan, investment, or depreciation is to be calculated. The periods may be defined in months, quarters, or years, but must agree with the increment used to define interest rate. |
| future value | The future value of an investment, given a certain present value, interest rate, and number of periods. |
| cost | The original cost of a depreciable capital asset. |
| salvage value | The remaining value of a capital asset after the depreciation period has expired. |
| allowable life | The allowable life of a depreciable item. |
| yield | The interest rate that will make the present value of the expected future cash flows equal to the price of the financial instrument. |
| price | The present value of the expected future cash flows where the discount rate is equal to the yield of the financial instrument. |
| coupon rate | The annual coupon rate of a security. |
| frequency | The number of coupon payments in a year. |
| basis | The day count basis to be used in calculation. |
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:
issue settlement maturity
issue first coupon maturity
issue last coupon maturity
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:
Dates are represented as an integer equal to the number of days since December 31, 1899, so January 1, 1900 equals 1.
Times are represented as fractions of a day, starting at midnight. For example, 6:00 AM is stored as 0.25 (a quarter of a 24-hour day).
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.
Type in the function name. Objective Grid recognizes the string as a function. Function names are abbreviations that indicate what the function does. For instance, ABS computes absolute value, ROUND rounds to the specified number of places, and AVG computes the average of a list of arguments. Function names may be preceded with an '@' sign, but this is not required.
After typing the function name, enter arguments in parentheses. Most functions use one or more arguments to define the task to be performed. For example, the @AVG function averages the value of two or more arguments. The @LENGTH function returns the length of an argument that is a character string.
Use only the arguments required by the function, in the exact order specified in the function syntax. If you enter other arguments or enter them in the wrong order, Objective Grid will misinterpret their meaning or return an error message.
All the function names in this chapter are typed in uppercase letters, but you can enter them in uppercase or lowercase for your entries.
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.
| Argument | Example |
| Numeric Value | 123 |
| Address of a cell | A10 |
| Address of a range | F9..F99 |
| String Value | "Quarterly Report" |
The result of a function depends on the order in which Objective Grid handles the calculations. Please see Section 14.4, "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.
To force Objective Grid to recalculate the entire spreadsheet you should call the Recalc() command. You should also add Recalculate menu in your application that calls Recalc().
You can also force Objective Grid to do a partial recalculation with respect to that cell, edit the cell and append a blank and press the [Return] key on the cell containing the @CCOUNT formula.
You can also use explicit dependencies to circumvent the limitation described above, if you entered the formula below in the form @CCOUNT(C3..J100,#A1 && #<A2)\A1\A2.
Objective Grid would take into account the dependencies on A1 and A2 and update the spreadsheet just as you expect.
Another approach is to construct the condition string with an expression that references the cells directly. For example, @CCOUNT(C3..J100, @STRCAT("#",A1,"&_<",A2)).
In this example, A1 and A2 are directly referenced and thus will properly trigger recalculation.
Explicit Dependency is described in more detail in Section 14.5.7, "Explicit Dependency."
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and Stingray, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.