NEW SPREADSHEET MODULE

Formulas are the backbone of a spreadsheet, establishing and calculating mathematical relationships between elements of the spreadsheet. Whereas numeric entries remain the same until you change them, cells defined by formulas are automatically changed to reflect changes in referenced cells - even where there are complex interdependencies among cells.

 

With the formula engine you can have up to a million rows and 4096 columns.

 

Here is some overview about the formula engine:

 

Cell Values

SimplexNumerica accepts several basic types of cell entries: text, constant numeric values, dates or times, formulas that calculate a value, and graphs. Calculated values may be single numbers or strings, or they can be arrays or tables of values.

 

Text Entries

Text entries are useful for labeling columns and rows, for including comments about data, values being calculated, and for using SimplexNumerica to manage textual information, such as names, addresses or whatever your application may require.

 

Numeric Values

If a cell entry begins with a digit from 0 - 9, SimplexNumerica treats the entry as a numeric entry. SimplexNumerica also recognizes the following symbols as indicators of numeric entries: ``+'', ``-'', and ``.''. You can format numeric values to be displayed in

several ways, including fixed formats, scientific notation, currency, and hex.

 

Dates and Times

SimplexNumerica provides special, built-in features for displaying date entries in the format you choose. Example date and time formats include: 24-Oct-91, 24-Oct, 10/24, Oct-91, 10/24/91, 24.10.1991, and 1991-10-24 (ISO 8061). Time is displayed as: 12:00:05.

 

Formulas

Formulas establish and calculate mathematical relationships between elements of the spreadsheet. SimplexNumerica formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily calculate the sum of a

series of cells, the total of values in a column, or the absolute value of another cell entry.

 

Referencing Other Cells in Formulas

The real power of SimplexNumerica lies in its ability to calculate relationships among different cells in the spreadsheet by typing the row/column coordinates, or address, in the formula.

 

To reference a cell by address you should Type the row and column coordinates of the cell in the formula. For example, to reference Row 5 in Column D, type D5. To reference a contiguous group of cells by address type the row and column coordinates of two cells in opposite corners of the block to be referenced, with two periods ( .. ) between the coordinates. For example, to reference the first five columns and the first five rows of the spreadsheet, type A1..E5.

 

Multilevel Undo/Redo

The formula engine supports multilevel undo/redo, e.g. when cell references get update because of a move operation undo needs to take care of these changes. But, all these changes don't affect your existing applications and the API is 100% compatible.

 

Named Ranges

SimplexNumerica allows you to assign a name to a range of cells. These cells can then be referenced via the range name.

 

Copying and Moving Cells

The Copy operation (either clipboard copy or ole drag & drop) duplicates a cell or range of cells (along with all formatting) and places these formulas in a new location, overwriting existing data in the destination range. SimplexNumerica automatically translates relative cell references in the copied formulas to reflect their new locations. For example, if cell A10 contains the formula =SUM(A1..A9) and is copied to cell B10, then B10 will contain the formula =SUM(B1..B9).

 

The Move operation moves a cell or range of cells to a new location, along with all formulas. SimplexNumerica clears the source cells and overwrites any existing data in the destination cells. Like the Copy Formulas operation, all cell references are updated

to reflect the new cell/range location.

 

Constraint Checking

Constraints are limitations or conditions placed on the variables in your spreadsheet. They are expressed as algebraic statements appended to formulas. You can attach a constraint expression to any formula, by typing a semicolon (;) and the constraint

conditions after the formula. For example, the formula =A1 + A2 ; #>2 && #<=B5 || #==C7 means, ``the value of the current cell is the sum of cells A1 and A2, and that value must be either greater than 2 and less than or equal to the value of cell B5, or equal to the

value of cell C7.'' You can turn on/off constraint checking via the Constraint Checking menu item!

 

Iterative Calculations    

Normally, a formula in a given cell should not depend on that cell itself, either directly or indirectly. Such a condition is called a cyclic dependency. When cyclic     dependencies exist, the rule for natural order recalculation as described above does not make sense.

When you enter a formula which creates a cyclic dependency, the message ``Cycle!'' is displayed in the cell.

In some cases cyclic dependencies are useful in that they can represent iterative calculations, which SimplexNumerica supports. Iterative calculation is useful when two or more cells mutually depend on each other such that each time they are recalculated, their

values become closer and closer to the desired answer.

Worksheet Functions

SimplexNumerica's functions are predefined formulas supplied with the program. 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 SimplexNumerica functions fall into the following categories:  

 

• Mathematical
• Statistical
• Conditional Statistical
• String
• Logic
• Digital Logic
• Financial
• Date and Time
• Miscellaneous
• Embedded Tools

 

Mathematical Functions

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.

 

Statistical Functions

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

 

Conditional Statistical Functions

Conditional Statistical Functions operate much like statistical aggregation functions, except that the last argument is a constraint expression that SimplexNumerica 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 SimplexNumerica expression that evaluates to a numeric result.

    

String Functions

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 upper to lower-case and vice versa, or replace one string with another.

 

Logic Functions

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.

 

Digital Logic Functions

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

 

Financial Functions

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 SimplexNumerica cover annuities, cash flows, assets. bonds, and Treasury Bills.

 

Date and Time Functions

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.

 

Miscellaneous Functions

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

Embedded Tools are a powerful feature in SimplexNumerica. 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.