Index Name
 CA-Compete! Help

Alert Messages

Commands

Functions

Glossary

Keyboard

Procedures

For information how to use Help:

press F1 or

choose Using Help  from the Help menu.


Alert Messages

All possible formulas cannot be displayed

Apply one formula at a time

Audit trail limit reached

Automatic calculation mode required

Bad CA-Compete! reference

Bad syntax in command

Can only goal seek on numerical values

Cannot close CA-Compete!

Cannot create file

Cannot goal seek on a cell with a value error

Cannot hide last visible object

Cannot merge, dimensions do not match

Cannot modify structure during recalculation

Cannot modify structure while applying a formula

Cannot interpret period

Cannot paste to complex range

Cannot perform task on a multiple selection

Cannot perform task on this selection

Cannot resolve circular reference

Cannot set formula scope during recalculation

Cannot set scope while another formula is being applied

Cell is protected

Copy and paste areas are different shapes

Could not initiate DDE link

Data table already in progress

dBase can be written from view selections only

dBase field too large

Dimension has spaces in its name

Dimension name  is too long

Dimension name already exists

Error in formula

Expression invalid - save and reload your work

File is 'read only'

File is not a Compete! 4.0 file

File record mark missing

Fill already in progress

Format too complex

Format too large

Formula references another object on this dimension

Formula too big

Formula too complex

Goal seek cannot converge to a solution

Goal seek could not find a solution

Illegal global reference

Illegal range reference

Illegal selection

Incomplete sheet reference

Incorrect password

Invalid  parameter

Invalid period

Invalid number of objects

Invalid paste area selection

Invalid sheet reference

List separator = decimal point

Minimum bound should be less than maximum

Missing '('

Missing ')'

Missing operand

Missing operator

Missing parameter

Model has only one dimension

Model must have at least two dimensions

Model to be merged cannot have more dimensions

Model to be merged must have same dimensions

Multiple outputs not allowed in this mode

Name already exists

Name already in use as a dimension

Name already in use as an object

No dimensional information found

No field information found

No formula available for this cell

No information describing field

No match

No match on current cell

No printer set up

Not enough memory for this operation

Numerical error occurred during goal seek

One dimension must be named 'Variables'

Only a 2-D selection can be pasted to a sheet

Only worksheets may be imported

Out of logical capacity

Output cell should contain a numerical value

Paste already in progress

Promotion of this cell would result in an illegal global reference

Protected cells in range

Range table invalid - save and reload your work

Reference not unique

Select an object on a dimension

Select an object to delete

Selection repeated

Selection should only vary across one dimension

Sheet allocation exceeded

Structure change in progress

Target cell does not depend on the source cell

Term expression not reversible

Too complex - save and reload before attempting this action

Too many exceptions defined

Too many external references

Too many formats

Too many page breaks

Too many ranges

Too many ranges in formula

Too many saved windows

Too many terms

Unmatched '('

Unmatched ')'

View allocation exceeded

View/Sheet reference mismatch

Wrong number of arguments

XLS file is password protected


All possible formulas cannot be displayed

All the possible formulas which may be applied to cells in the selected range cannot be shown due to lack of display space. You should select a smaller or less complex range which has a more restricted set of possible formulas.


Apply one formula at a time

You may not apply a global formula while another global formula is still being applied to the database. You should wait until the first formula has been applied before proceeding. (You may terminate the application of the first formula by hitting the ESC key.)


Audit trail limit reached

Using an audit trail relationships between database cells may be traced up to a limit of 50 levels. In most cases this is sufficient. If however, you find that you wish to proceed with an audit beyond this limit you may open a new audit trail which starts at the end point of the first one.


Automatic calculation mode required

Certain operations cannot be carried out while the system is in manual calculation mode.

If you get this message, you should set the calculation mode to automatic using the Options Calculation menu item dialog.

Operations which require automatic calculation mode are:

- Goal Seek

- Data Table


Bad CA-Compete! reference

A bad CA-Compete! reference was detected during a script file import.

For instance a reference like P1.Units.USA = 5.00 could not be resolved if no object called P1 had been earlier defined.


Bad syntax in command

Incorrect comand syntax was detected during a .COL format file import.


Can only goal seek on numerical values

It is not possible to perform a goal seek on a cell which contains a non-numerical value (e.g. text or logical).


Cannot close CA-Compete!

CA-Compete! cannot be closed down while it is performing certain tasks. These include:

- Executing a fill or paste operation

- Making a structure change

- Recalculating the database

- Printing

This message should inform you which task currently in progress is preventing CA-Compete! from closing down. Should you wish to close CA-Compete! rather than wait, you may in general terminate the task early by pressing the ESC key.


Cannot create file

CA-Compete! could not create the required dBase file. This problem may be due to lack of disk space or because you attempted to create the file on a drive with restricted access (such as some network drives).


Cannot goal seek on a cell with a value error

It is not possible to perform a goal seek on a cell with a value error - indicated by  #VAL, #NUM , #DIV/0 or similar. If you get this message you should alter the initial value of the goal seek  source cell so that the target cell does not contain a value error flag.


Cannot hide last visible object

It is not possible to hide all the objects on a dimension. At least one object per dimension must remain visible.


Cannot merge - dimensions do not match

The dBase file which you are trying to inport cannot be merged wih the existing model because it's dimensions do not match those of the model. Check the dimension names in the dBase file.

Note: In this case, CA-Compete! is not case-sensitive (ie "REGIONS" is matched with "Regions").


Cannot modify structure during recalculation

It  is not possible to make structure changes to a model while recalculation of the database is in progress. You should wait for the recalculation to finish before proceeding with the structure change. Alternatively you may terminate the recalculation by hitting the ESC key.


Cannot modify structure while applying a formula

It  is not possible to make structure changes to a model while application of a global formula to the database is in progress. You should wait for the application of the formula to finish before proceeding with the structure change. Alternatively you may terminate the application of the global formula by hitting the ESC key.


Cannot interpret period

The forecast interval driver which you have entered into the "Define Periods" box in the Tools Forecast dialog cannot be meaningfully interpreted. You should check that you have correctly entered the name of the driver object or dimension. Mistakes can often be avoided if you create references using point and click techniques with a mouse.


Cannot paste to complex range

It is in general only possible to paste to a complex range if its extent matches the original range from which the data was copied or is an integral multiple thereof.


Cannot perform task on a multiple selection

Certain operations can only be performed on a contiguous selection of cells.

These operations include :

- Cut

- Copy

- Exporting to a script file

You may proceed by subdividing the selection into a set of contiguous selections and performing the operation on each selection in turn. In the case of a script file this would involve saving a separate file for each selection. Note that for many operations, the selection can be multi-dimensional.


Cannot perform task on this selection

Certain operations which may be performed on discontiguous ranges, require that the selected range of cells possesses a certain geometric regularity.  For example, it is not possible to simultaneously  perform a forecast on two separate objects where the selection references six objects on the forecast dimension in one case and only five in the second case.

Operations which may not be performed on irregular selections include:

- Tools Forecast

- Tools Allocation


Cannot resolve circular reference

During calculation of the model, a circular reference or cycle was detected.  This means that a cell to be calculated actually depends on itself due to the nature of the formulas which have been applied to the model. It is not possible to resolve this situation in Automatic Calculation mode. You may either change the formulas which have produced the circular reference or enter Manual Calculation mode and use iteration to produce a result. To change calculation modes use the Options Calculation... menu item.


Cannot set formula scope during recalculation

It  is not possible to set the scope of a global formula while calculation of the model is in progress. You should wait for the calculation to finish before setting the formula scope. Alternatively you may terminate the calculation by hitting the ESC key.


Cannot set scope while another formula is being applied

It  is not possible to set the scope of a global formula while the application of another  global formula to the database is in progress. You should wait for the application of the first formula to finish before setting the scope of the second formula.


Cell is protected

You have tried to alter the contents of a cell which has been protected. If you wish to modify the data contained in this cell you must first remove the protection using the Format Unprotect Cells menu item.


Copy and paste areas are different shapes

You have tried to paste data into an area whose extent is not equal to or an exact multiple of the area from which the data was originally copied.


Could not initiate DDE link

An error occurred during the initation of a DDE link which is produced by the Edit Paste Links menu option or by entering an EXTERN formula. The most likely causes of this error are either a failure to execute the remote application or a failure to access the specified data. First check the specification of the directory path to the remote application and then the specification of the data to be linked.


Data table already in progress

A data table is already being calculated. You should allow the calculation to complete before starting a new data table. You may terminate the first data table early by hitting the ESC key.


dBase can be written from view selections only

You cannot write a dBase file using a selection in a CA-Compete! sheet.


dBase field too large

The field of a dBase record read during a file import is too large to be loaded into the model.


Dimension has spaces in its name

You have tried to create a dBase file from a model with a dimension whose name contains one or more spaces. The dBase format does not allow the use of field names containing spaces so you should rename the dimension in your CA-Compete! model. Note: The underscore character _ is allowed so this can be used to make the dimension name more readable.


Dimension name is too long

The dBase format only allows field names of up to 10 characters - use dimension names and object names on the Variables dimension of 10 or less characters in length.

Note: One dimension must be named Variables - this is a restriction CA-Compete! imposes for dBase format exchange to allow the the field and record structure to be determined from the CA-Compete! dimension and object structure.


Dimension name already exists

There is already a dimension in the model which uses the name you have given. CA-Compete! does not support non-unique dimension names.

Note: Non-unique object names are possible and CA-Compete! distinguishes between them by appending a suffix when creating references to non-unique objects.


Error in formula

The formula which you entered could not be interpreted correctly.  It is likely that an object name has been misspelt  or that the formula contains illegal characters.


Expression invalid - save and reload your work
Expression dependencies invalid - save and reload your work

A bad expression has been found in the database. This is a serious error and it is not advisable to continue working with the model. If you save and reload your work CA-Compete! will attempt to resolve the problem.

Warning: The reload of the model will take considerably longer than usual and the message "Reconstructing espression dependencies" will appear in the status window at the bottom of the CA-Compete! window.

A confidence test may be performed by simultaneously holding down the Ctrl+Shift+P keys. This test runs through the model and checks integrity and attempts to fix any problems found.


File is 'read only'

You have attempted to write output to a file which is designated as 'read only'. If you really want to overwrite the file use a utility such as Norton Utilities or Xtree to change the attributes of the file and then overwrite it.


File is not a CA-Compete! 4.x file

CA-Compete! does not recognise the .MDL file which you have tried to load as a correctly formatted CA-Compete! 4.x file. If the file should be a CA-Compete! file then it may have been corrupted by a disk problem or during transfer from floppy disk.


File record mark missing

This MDL file has been corrupted.


Fill already in progress

There is already a fill operation in progress. You should wait until it is completed before proceeding with the next fill. You may also terminate the current fill by hitting the ESC key.


Format too complex

The syntax of the format you have defined is too complex, probably containing too many separate clauses.


Format too large

The maximum allowable length is dependent on the nature of the format formula - in general, about 60-80 characters per clause are allowed.


Formula references another object on this dimension

It is not possible to make a formula global across a dimension, if the formula references an object of this dimension.


Formula too big

The maximum allowable length is dependent on the nature of the formula - in general, about 300-500 characters per formula are allowed.


Formula too complex

The formula you have tried to enter is too complex, probably containing too many nested calculations. You should try to simplify the formula or redefine it using the results of intermediate calculations.


Goal seek cannot converge to a solution

Goal seek could not find a solution to within the specified error tolerance. The error tolerance which you have specified may be too stringent.


Goal seek could not find a solution

Goal seek could not locate a possible solution within the specified constraints for the source cell. To achieve the target which you have set, it may be necessary to alter the minimum/maximum constraints on the source cell.


Illegal global reference

A reference between global and worksheet-level space is illegal. References must be between cells of the same global level.

For instance, in a model with dimensions REGIONS, TIME and FINANCIALS  the cell REGIONS.1989.FINANCIALS  cannot contain a reference to USA.1989.FINANCIALS or to REGIONS.TIME.FINANCIALS but to REGIONS.1993.FINANCIALS


Illegal range reference

A range reference is defined as the rectangular area between two cell references. You cannot have more than two cells building up a range reference.


Illegal selection

The cell which you have chosen as the allocation source  ("Based on") in the Tools Allocation dialog is not a valid choice.


Incomplete sheet reference

In giving the starting point for the output of a data table, you have only given a sheet name and omitted to give the starting coordinate for the tabulation.

For instance: Sheet 1 instead of Sheet 1.A1.


Incorrect password

The password which you have entered was incorrect. Be very careful about password protecting files since there is no way to open the file again if you forget the password. Computer Associates can recover the file for you if neccessary but this will require sending the file to Computer Associates and validation of access rights to the file.


Invalid  parameter

A parameter which you have entered into a Tools dialog box cannot be meaningfully interpreted or else is inappropriate in the given context. The most usual cause of this error is a misspelt or non-existent object name. Mistakes can often be avoided if you create references using point and click techniques with a mouse.


Invalid period

The driver which you have entered in the "Define Periods" box of the Tools Forecast dialog is not appropriate in the given context. For example, you may not define the forecast periods using an object on the dimension along which you are forecasting.


Invalid number of objects

The maximum number of objects allowed on a dimension is 16384. The actual number of objects which fit on a dimension is also limited by the length of the names you choose. The total length for all the object names on any dimension is 65535 characters (including 1 extra terminator character for each name).


Invalid paste area selection

The area which you have selected for the Edit Paste operation is invalid. Ranges which overlap the copied range are in general not allowed.


Invalid sheet reference

A reference to a sheet was made giving either an incorrect name or an invalid sheet coordinate.


List separator = decimal point

The list separator and the decimal point delimiters have both been defined using the same character. This may cause some features to work incorrectly and result in ambiguity in formulas which you enter. You should rectify this situation by changing either the decimal point or list separator setting in the International Settings dialog of the Windows Control Panel.


Minimum bound should be less than maximum

The minimum bound for a goal seek should be less than the maximum bound.


Missing '('

During the interpretation of the formula which you tried to enter, a missing left parenthesis (open bracket) was detected.


Missing ')'

During the interpretation of the formula which you tried to enter, a missing right parenthesis (close bracket) was detected.


Missing operand

The formula which you tried to enter contained an arithmetical operator (such  as + or  *)  which was not followed by an appropriate operand.


Missing operator

The formula which you tried to enter was syntactically incorrect because an arithmetic operator was omitted. This could happen if you tried to enter a formula such as "= price * units", but omitted the * operator or inserted an invalid character in its place.


Missing parameter

You have omitted to enter a parameter in a Tools dialog box. The message informs you which parameter you have omitted.


Model has only one dimension

The model which CA-Compete! has just loaded has only one dimension and a view cannot be displayed.  The most likely cause of this problem is an import from an incorrectly structured text file.


Model must have at least two dimensions

You have tried to delete a dimension in a CA-Compete! model which has only two dimensions - a one-dimensional model is not possible.


Model to be merged cannot have more dimensions

CA-Compete! has detected that the file which you are trying to merge is based on a  model with more dimensions than the current structure. It is only possible to merge a model of equal or lower dimensionality.


Model to be merged must have same dimensions
Model to be merged must have same dimension names

CA-Compete! has detected that one or more of the dimension names in the model which you are trying to merge do not match with the dimension names of the current structure. In this case CA-Compete! cannot meaningfully merge the data into the current structure. You should check that the dimensions names in the file being imported match exactly with the dimension names of the current structure.


Multiple outputs not allowed in this mode

If you have selected the two input mode for a data table, you may only specify one output cell.


Name already exists

You have tried to name an object with a name which is already in use. CA-Compete! supports the use of non-unique names for objects but warns you in case you prefer to avoid any possible ambiguity.


Name already in use as a dimension

An object may not be given the same name as a dimension.


Name already in use as an object

A dimension may not be given the same name as an object.


No dimensional information found

No dimensional information found in imported file. This occurs due to an incorrectly structured text file.


No field information found

No field information found in .COL format import file. Check the .COL file format syntax in the CA-Compete! reference manual.


No formula available for this cell

An attempt was made to change the formula scope for a cell with no formula.


No information describing field

A field was found during the import of a .COL file which had not been previously defined.  Check the .COL file format syntax in the CA-Compete! reference manual.


No match

CA-Compete! cannot find what you are searching for in the current selection. There were either no instances of the specified text or all instances have been found and replaced.


No match on current cell

CA-Compete! cannot find what you are looking for in the currently selected cell.


No printer set up

Use the File Printer Setup menu option to set up a printer. If there are no printers installed, use the Windows Control Panel to install one first.


Not enough memory for this operation

The operation which has been attempted requires more memory than is currently available. You should try to increase the amount of available memory by closing down  other programs which are currently running.


Numerical error occurred during goal seek

In the course of a goal seek, a numerical error occurred and the goal seek had to be abandoned. You should change the minimum/maximum bounds on the source cell to exclude values which may produce numerical errors (e.g. negative numbers as arguments of the SQRT function or values which will cause divide by zero errors).


One dimension must be named 'Variables'

If you want to write out the data in your CA-Compete! model in dBase format you must indicate to CA-Compete! which dimension is to be taken as the primary dimension for constructing the dBase records. This is done by naming that dimension 'Variables'.


Only a 2-D selection can be pasted to a sheet

You may only paste a two-dimensional selection into a sheet. CA-Compete! cannot determine how to arrange a selection of higher dimensionality within a sheet which is a two-dimensional structure. You may paste a three or higher-dimensional selection into a sheet as a series of two-dimensional slices.


Only worksheets may be imported

You may only import Microsoft Excel worksheet files (.XLS). Macro and chart files may not be imported.


Out of logical capacity

Your model has grown too large in terms of its logical capacity. It is not possible to add any more objects or dimensions to this model.

The capacity of a CA-Compete! model is 2 billion cells. For example, a model with 5 dimensions, each containing 10 objects has a logical capacity of 100000 cells (10*10*10*10*10). There are limitations in how the capacity is shared out - a dimension which has less than (2^n-1) objects (where n is an integer) wastes some space from the maximum logical capacity. For maximum use of the capacity, use dimensions with 3, 7, 15, 31, 63, 127, 255, 511, 1023, 2047 etc objects.


Output cell should contain a numerical value

One or more of the output cells which you have selected for a data table contains a non-numerical value.


Paste already in progress

A paste operation is already in progress. You should wait for it to finish before proceeding with the next paste. You may also terminate the current paste by pressing the Escape key.


Promotion of this cell would result in an illegal global refere
Promotion of this cell would result in an illegal global reference

The formula in the selected cell contains an absolute database reference and consequently may not be promoted to global level.


Protected cells in range

The range which you selected for the operation which has just been performed contained some protected cells. Since these cells are protected they were not modified. If you did actually wish to modify these cells you should first unprotect them using the Format Unprotect Cells menu option and then repeat the operation on the selected range.


Range table invalid - save and reload your work

The internal range table which CA-Compete! uses for resolving references to ranges has been corrupted. This is a serious error and will very rarely occur.  If you get this message, however, it is not advisable to continue working with the model. If you save and reload your work CA-Compete! will attempt to patch up the bad expression.


Reference not unique

You have made a reference to a non-unique object name. This reference is ambiguous and CA-Compete! requires you to fully specify the object which you wish to reference. You may do this by adding the dimension name and object index in brackets after the object name. You may also generate the reference using the 'point and click' technique if you have a mouse. This will automatically append the dimension name and object number which CA-Compete! requires to generate an unambiguous reference.


Select an object on a dimension

The action which you have specified requires you to select one or more objects on a dimension. There is currently no selected object.


Select an object to delete

You should select the object of objects which you wish to delete. There is currently no selected object.


Selection repeated

The exception list which you have specified in the Tools Allocation dialog for partial allocation is invalid because one or more items in this list are repeated.


Selection should only vary across one dimension

The exception list for a partial allocation operation using the Tools Allocation dialog should contain references which only vary across one dimension.


Sheet allocation exceeded

The maximum number of sheets which you may have in any one model is 1024.


Structure change in progress
Structure change already in progress

A structure change is already in progress and the action which you wish to perform requires a further structure change which cannot be carried out until the first structure change has been completed.


Target cell does not depend on the source cell

Modifying the source cell during the goalseek did not produce any detectable changes in the value of the target cell. You should use the Info window or an Audit Trail to check that the target cell does in fact depend on the source cell.


Term expression not reversible

You have tried to enter data in a term which is non-reversible. When you enter data in a term (i.e. in a view where a term is applied), the data is interpreted relative to the term. For example, if the applied term is defined to be =X/2 (so that the data is displayed as half its intrinsic value) a number entered (e.g. 5.3) is actually entered into the model as =(5.3)*2.


Too complex - save and reload before attempting this action

Under certain circumstances, adding more than a certain number of objects to a dimension is not possible until the model has been saved and reloaded. You may then proceed to delete the dimension.


Too many exceptions defined

The maximum number of term exceptions which you may define is 100.


Too many external references

CA-Compete! currently supports up to 256 external links.


Too many formats

The maximum number of formats which you may define is 64


Too many page breaks

The current maximum number of page breaks allowed is 10 each along the horizontal and vertical axes, giving an total limit of 100 pages.


Too many ranges
Too many  print ranges

Currently up to 5 print ranges are supported.


Too many ranges in formula

Too many ranges in formula.


Too many saved windows

The maximum number of windows  which you may save per model is 2048, i.e.  up to1024 sheets and up to 1024 views.


Too many terms

The maximum number of terms which you may define is 65534.


Unmatched '('

The formula which you have tried to enter is missing a right parenthesis ')' or contains an extra left parenthesis '('


Unmatched ')'

The formula which you have tried to enter is missing a left parenthesis ')' or contains an extra right parenthesis ')'


View allocation exceeded

The maximum number of views which may be opened in any one model is 1024. This limit has now been reached and it is not possible to open any more views of the database.


View/Sheet reference mismatch

You have incorrectly mixed view and sheet references in a Tools dialog box. For example, you may not allocate cells in a view using a cell in a sheet as the "Based on" reference or vice versa. Other examples of illegal mixing of references are:

- Using a sheet reference to define forecast periods where the forecast is being carried out in a view or vice versa (Tools Forecast dialog).

- Using a sheet reference as a cost  function driver where the cost function is being entered in a view or vice versa (Tools Response Functions dialog).

One important case in which mixing references is quite legal is the use of values stored in sheets to define response functions.


Wrong number of arguments

The function which you have tried to enter has an incorrect number of arguments. The correct syntax  is given by the Edit Paste Function menu item with the Paste Arguments option selected. You may check the function syntax in this Help file under the Functions topic or consult the  CA-Compete! Function Reference Manual (Function Specifications).


XLS file is password protected

CA-Compete! cannot import an XLS (Microsoft Excel) format file which is password protected. Reload the XLS sheet in Excel and remove the password protection first.


CA-Compete! Commands

Audit Menu

Edit Menu: copy/paste commands

Edit Menu: structure edit commands

File Menu

Format Menu

Formula Menu

Help Menu

Info Menu

Options Menu

Tools Menu

Window Menu


File Menu
CA-Compete! File Menu

New 	Starting a Model

Open	Start or merge a saved model.

Open an Existing Model

Links	Change the name of linked documents.

Save	Save a model.

Save As	Save a model under a new name.

Print	Print  the data specified by printarea

Page Setup 	Page Setup

Printer Setup 	Setup printers

Exit	Exit CA-Compete!

Realted Topics:	CA-Compete! Commands


Formula Menu
CA-Compete! Formula Menu

Paste Object...	Brings up the object selector to paste selection.

Paste function	Brings up functionname selector to allow pasting functionnames

Reference	Toggle between absolute and relative references.

Note	Toggle between annotation and formula mode.

Goto	Select a cell to become the active one.

Find	Find text in formulas of selected cells

Replace	Replace Text in formulas of selected cells with new text.

Apply Formula	Apply a global formula to a cell or a group of cells or reinstall the worksheet formula.

See also:Applying Global Formulas

Formula Scope	Set the scope for a global formula.

See also: Defining Formula Scope

Set Term	Apply a term to a view.

See also: Create a term

Realted Topic: 	CA-Compete! Commands


Format Menu
CA-Compete! Format Menu

Number	Apply a number format to the selected cells.

Alignment	Set the alignment for the selected cells.

Font	Set a font for the selected cells.

Border	Set the border attributes for the selected cells.

Protect	Protect  the selected cells against later changes.

Unprotect cells	Unprotect  the selected cells to enable later changes.

Set protect password	Set a password to disable unauthorized unprotection.

Row Height	Set the row height for the current selection.

Column Width	Set the Column Width for the current selection.

Realted Topics:	CA-Compete! Commands


Options Menu
CA-Compete! Options Menu

Set Print Area	Set the area to be print at the print command.

Clear Print Area	Clear the area set by the 'Set Print area' command.

Set Page Break	Set page break.

Show 	Show hidden objects.

Hide	Hide visible objects.

Display 	Set display options.

Preferences	Set model preferences.

Calculation	Set calculation options.

Calculate now	Start  calculation.

Workspace	Set global display options.

Short menus	Disable some commands.

Full menus	Enable all commands.

Realted Topic:	CA-Compete! Commands


Tools Menu
CA-Compete! Tools Menu

Allocate	Enter data using the allocation dialog.

Response Functions	Enter data using the response function dialog.

Forecast	Enter data using the forecast dialog.

Audit Trail	Show cell dependencies

Goal Seek	Modify data by goalseeking.

Data Table	Create a data table.

Realted Topics:	CA-Compete! Commands


Window Menu
CA-Compete! Window Menu

New Window	Start a new view or a new sheet.

Open Window	Open or delete a previosly named Window.

Name Window	Naming a View or a Sheet

Show Info	Open the Info window.

Arrange All	Arranges all windows side by side, so that they are all visible.

Hide	Hide the active window.

Unhide	Show a previoslly hidden window.

Window1	Make Window1 active

Realted Topics:	CA-Compete! Commands


Help Menu
CA-Compete! Help Menu

Help	Start Win-Help.

About	Show the About Box.

Realted Topics:	CA-Compete! Commands


Audit Menu
CA-Compete! Audit Menu

Jump	Select the cell.

Realted Topics: 	CA-Compete! Commands


Info Menu
CA-Compete! Info Menu

Turns on/off following information about the active cell:

Cell Name	The full name of the cell.

Active Formula	The active formula of the cell.

Value 	The current value of the cell.

Protection	The protection state of the cell.

Note 	The annotations.

Available Formulas	All for the active cell available global formulas and the worksheet formula.

Available Values	The result values of all available global formulas and the worksheet formula.

Dependent Cells	A list of cell, which dirctly depend on the active cell.

Realted Topics: 	CA-Compete! Commands


Copy Paste Commands

Cut	Copy selection to clipboard and erase it on paste

Copy	Copy selection to clipboard.

Paste	Copy contents of clipboard to selection

Clear	Erase Selection.

Paste Special	Copy contents of clipboard to selection using options.

Paste Link	Install a hot link ...

Fill Up Left(select Edit Menu with Shift key down )

Down Right:	Copy contents from downmost (rightmost,upmost,leftmost)  cell(s) to all cells in selection.

Select All	Select all cells in active window.

Realted Topics: 	CA-Compete! Commands

structure edit commands


Structure Edit Commands
Structure Edit Commands:

Append	Append  new objects right after the currently selected ones.

Insert	Insert  new objects before the currently selected ones.

Delete	Delete the selected objects from the model

Add Dimension	Add a new dimension to the model

Rename Dimension	Change a dimensionname of the model

Set Structure Password	Protect model from structure changes

Realted Topics: 	CA-Compete! Commands

copy/paste commands


Functions by topic

Date and Time

DDE and DLL

Financial

Information

Logical

Lookup

Mathematical

Statistical

Text

Trigonometric

Related Topic:

Functions by alphabet


Date and Time Functions

DATE

DATEVALUE

DAY

HOUR

MINUTE

MONTH

NOW

SECOND

TIME

TIMEVALUE

WEEKDAY

YEAR

Related Topics:

Functions by topic

Functions by alphabet


Financial Functions

CAGR

DDB

FV

IPMT

IRR

MIRR

NPER

NPV

PMT

PPMT

PV

RATE

SLN

SYD

Related Topics:

Annuity Functions

Functions by topic

Functions by alphabet


Annuity Functions

An annuity is a series of constant cash payments made over a continuous period of time.  For instance, the payment schedule for a car loan or a mortgage is an annuity.

In annuity functions, cash you pay out, such as deposits to savings or loan payments, is represented by a negative number; cash you receive, such as dividend checks or a loan, is represented by a positive number.  For example, a $200 deposit to the bank is represented by the argument  -200 if you are the depositor and 200 if you are the bank.

The following CA-Compete! functions apply to annuities:

FV(rate,nper,pmt,[pv],[type])

IPMT(rate, per,nper,pv,[pv],[type])

NPER(rate,pmt,pv,[fv],[type])

PMT(rate,nper,pv,[pv],[type])

PPMT(rate, per , nper,pv,[pv],[type])

PV(rate,nper,pmt,[fv],[type])

RATE(nper,pmt,pv,[fv],[type],[guess])

CA-Compete! solves annuity functions for one financial argument in terms of the others.  If the rate is not 0, then:

pv*(1+rate)^nper+pmt*(1+rate*type)*((1+rate)^nper-1)/rate+fv=0

If the rate is 0, then:

pv+pmt *nper+fv=0

Related Topics:

Financial Functions

Functions by topic

Functions by alphabet


Information Functions

CELL

ISBLANK

ISCONSISTENT

ISERR

ISERROR

ISLOGICAL

ISNA

ISNONTEXT

ISNUMBER

ISREF

ISTEXT

N

NA

SIZE

T

TYPE

Related Topics:

Functions by topic

Functions by alphabet


Logical Functions

AND

FALSE

IF

NOT

OR

TRUE

Related Topics:

Functions by topic

Functions by alphabet


Lookup Functions

CHOOSE

DIMENSION

DIMENSIONS( )

DIMENSIONNAME

INDEX

LABEL

LINLINS

LINLOGS

LOGLOGS

LOOKUP

MATCH

OBJECTINDEX

OBJECTNAME

Related Topics:

Functions by topic

Functions by alphabet


Math Functions
Mathematical Functions

ABS

ALLOC

EXP

FACT

INT

LN

LOG

LOG10

MOD

PI

PRODUCT

RAND

ROUND

SIGN

SQRT

TRUNC

Related Topics:

Functions by topic

Functions by alphabet


DDE Functions
DDE and DLL Functions

DLL

EXTERN

EXTERN$

Related Topics:

Functions by topic

Functions by alphabet


Statistical Functions

AVERAGE

COST

COST2

COUNT

COUNTA

GROWTH

GROWTH2

LINLINEST

LINLOGEST

LOGLOGEST

MAX

MIN

SHARE

STDEV

STDEVP

SUM

TREND

TREND2

VAR

VARP

VECTOR

Related Topics:

Functions by topic

Functions by alphabet


Text Functions

CHAR

CLEAN

CODE

DOLLAR

EXACT

FIND

FIXED

LEFT

LEN

LOWER

MID

PROPER

REPLACE

REPT

RIGHT

SEARCH

SUBSTITUTE

TEXT

TRIM

UPPER

VALUE

Related Topics:

Functions by topic

Functions by alphabet


Trigonometric Functions
Trigonometrical Functions

ACOS

ASIN

ATAN

ATAN2

COS

SIN

TAN

Related Topics:

Functions by topic

Functions by alphabet


Functions by alphabet

ABS()

ACOS()

ALLOC()

AND()

ASIN()

ATAN()

ATAN2()

AVERAGE()

CAGR()

CELL()

CHAR()

CHOOSE()

CLEAN()

CODE()

COS()

COST()

COST2()

COUNT()

COUNTA()

DATE()

DATEVALUE()

DAY()

DDB()

DIMENSION()

DIMENSIONNAME()

DLL()

DOLLAR()

EXACT()

EXP()

EXTERN()

EXTERN$()

FACT()

FALSE()

FIND()

FIXED()

FV()

GROWTH()

GROWTH2()

HOUR()

IF()

INDEX()

INT()

IPMT()

IRR()

ISBLANK()

ISCONSISTENT()

ISERR()

ISERROR()

ISLOGICAL()

ISNA()

ISNONTEXT()

ISNUMBER()

ISREF()

ISTEXT()

LABEL()

LEFT()

LEN()

LINLINEST()

LINLINS()

LINLOGEST()

LINLOGS()

LN()

LOG()

LOG10()

LOGLOGEST()

LOGLOGS()

LOOKUP()

LOWER()

MATCH()

MAX()

MID()

MIN()

MINUTE()

MIRR()

MOD()

MONTH()

N()

NA()

NOT()

NOW()

NPER()

NPV()

OR()

OBJECTINDEX()

OBJECTNAME()

PI()

PMT()

PPMT()

PRODUCT()

PROPER()

PV()

RAND()

RATE()

REPLACE()

REPT()

RIGHT()

ROUND()

SEARCH()

SECOND()

SHARE()

SIGN()

SIN()

SIZE()

SLN()

SQRT()

STDEV()

STDEVP()

SUBSTITUTE()

SUM()

SYD()

T()

TAN()

TEXT()

TIME()

TIMEVALUE()

TREND()

TREND2()

TRIM()

TRUE()

TRUNC()

TYPE()

UPPER()

VALUE()

VAR()

VARP()

VECTOR()

WEEKDAY()

YEAR()

Related Topic:

Functions by topic


ABS()
ABS(number)

Purpose: The ABS function determines the absolute value of a number. An absolute number is a number without its sign (+ or -).

Arguments: number The number for which you want the absolute value.  This argument can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results:  If the argument is negative, the function returns the same number but as a positive; if the argument is positive, the function has no effect on the number.

Examples:

ABS(5) equals 5

ABS(-5) equals 5

If the cell A.'3 contains the value -75, then:

ABS(A.'3) equals 75.

If the cell A.'3 contains the positive value 75, then:

ABS(A.'3) equals 75.

Related functions:

SIGNreturns the sign of a number as a value:  1 (positive), -1 (negative), and 0 (zero).

Mathematical Functions


ACOS()
ACOS(number)

Purpose: The ACOS function returns the arccosine of a number.  The arccosine is the radian measure of the angle whose cosine is the argument.

Arguments: number The cosine of the angle for which you want to determine the measure.  This argument can be a number, text repre-sentation of a number, formula that returns a number, or reference to a cell that contains any of these, and must be in the range -1 to 1.

Results: This function returns a value that represents the radian measure of an angle in the range 0 to π.  If you want to convert the result from radians to degrees, multiply the result by 180/PI().

If the argument is a number greater than 1 or less than -1, the function returns the error value #NUM! or #DIV/0!.

ACOS always returns a value between 0 and 3.14 radians (180 degrees).

Examples:If you want to know the radian measure of the angle whose cosine is 0.5, then:

ACOS(.5) equals 1.047198 (1π/3 radians).

If you want to know the degree measure of the angle whose cosine is 0.5, then:

ACOS(.5)*180/PI() equals 60 (degrees).

Related functions:

COS returns the cosine of an angle.

PI returns the value π.

Trigonometric Functions


ALLOC()
ALLOC(total,value1,value2)

Purpose: The ALLOC function spreads a specified total in the proportion specified by the arguments.

Arguments:total The total value you want to allocate.

value1 The numerator in the ratio by which you are allocating total.

value2 The denominator in the ratio by which you are allocating total.

Arguments in the ALLOC function can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.  If an argument contains a logical value or text that cannot be translated, ALLOC returns the error value VALUE!.  If an argument contains an error value, ALLOC returns that error value.

Results:A value that is in the same proportion to total as value1 is in proportion to value2.

The ALLOC function uses the following formula to calculate a value:

total*value1/value2

Therefore, the resulting value divided by total is equal to the ratio of value1 to value2.

Use: ALLOC is the function used by the Tools Allocate command when you allocate a total to cells in the range defined by the allocation scope.  For more information on the Tools Allocate command and different allocation methods, see the topic Allocation.

Examples:

ALLOC (100,40,400)equals10

ALLOC("70",30,60) equals 35

Related functions:

Mathematical Functions


AND()
AND(logical1,[logical2], . . .)

Purpose:The AND function determines if all of the values specified by the arguments are true.

Arguments:

logical A logical value (TRUE or FALSE), a formula, a conditional test, or a reference to a cell or range that contains a logical value.

The AND function can have from 1 to 40 arguments.  If a reference argument contains text or empty cells, those values are ignored.  An argument can reference a multidimensional range.

Results: The logical value TRUE if all of the values specified by the arguments are true.  If any of the values specified by the arguments is false, AND returns the logical value FALSE.

If there are no logical values specified, the function returns the error value #VALUE!.

Use: The AND function is used for more complex conditional testing in your models.  This function works in conjunction with the simple operators =, >, <, >=, <=, <> and with the functions IF, OR, and NOT.  Together, you can use these functions to develop compound conditions.

Examples:

AND(TRUE(),TRUE()) equals TRUE

AND(FALSE(),TRUE()) equals FALSE

AND(1+2=3,1+1=2) equals TRUE

If the range B.'1:B.'4 contains the values TRUE, TRUE, TRUE, and FALSE, then:

AND( B.'1:B.'4) equals FALSE.

If the cell Units.'1989 contains the number 425, then:

AND(Units.'1989>400,Units.'1989<600) equals TRUE.

If the cell Units.'1989 contains the number 300, then:

AND(Units.'1989>400,Units.'1989<600) equals FALSE.

Related functions:

NOT returns the logical value that is the reverse of its argument.

OR returns the logical value TRUE if any argument is true.

Logical Functions


ASIN()
ASIN(number)

Purpose: The ASIN function returns the arcsine of a number.  The arcsine is the radian measure of the angle whose sine is the argument.

Arguments: numberThe sine of the angle for which you want to determine the measure.  This argument can be a number, text repre-sentation of a number, formula that returns a number, or reference to a cell that contains any of these, and must be in the range -1 to 1.

Results: This function returns a value that represents the radian measure of an angle in the range -π/2 to π/2.  If you want to convert the result from radians to degrees, multiply the result by 180/PI().

If  theargument is a number greater than 1 or less than -1, the function returns the error value #NUM! or #DIV/0!.

ASIN always returns a value between -1.57 and 1.57 radians (90 degrees).

Examples: If you want to know the radian measure of an angle whose sine is 0.5, then:

ASIN(.5) equals .524 (1π/6 radians).

If you want to know the degree measure of an angle whose sine is 0.5, then:

ASIN(.5)*180/PI() equals 30 (degrees).

Related functions:

SIN returns the sine of an angle.

PI returns the value π.

TrigonometricFunctions


ATAN()
ATAN(number)

Purpose: The ATAN function returns the arctangent of a number.  The arctangent is the radian measure of the angle whose tangent is the argument.

Arguments: number The tangent of the angle for which you want to determine the measure.  This argument can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

.

Results:  This function returns a value that represents the radian measure of an angle in the range -π/2 to π/2.  If you want to convert the result from radians to degrees, multiply the result by 180/PI().

 ATAN always returns a value between -1.57 and 1.57 radians (90 degrees).

Examples: If you want  to know the radian measure of an angle whose tangent is -1, then:

ATAN(-1) equals -.785 (-π/4 radians).

If you want to know the degree measure of an angle whose tangent is -1, then:

ATAN(-1)*180/PI() equals -45 (degrees).

Related functions:

ATAN2 returns the arctangent of an angle defined by a point and the x-axis.

TAN returns the tangent of a number.

PI returns the value π.

Trigonometric Functions


ATAN2()
ATAN2(x_number,y_number)

Purpose: The ATAN2 function returns the four-quadrant arctangent of the tangent described by the arguments.  The arctangent is the radian measure of the angle from the x-axis to the point defined by the two arguments.

 Arguments: x_number The x-axis coordinate of the point that determines the angle you want to measure.

y_number The y-axis coordinate of the point that determines the angle you want to measure.

Either argument can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: The function returns a value that represents the radian measure of an angle in the range -π to π, excluding -π.  A positive result represents a counter-clockwise angle from the x-axis and a negative result a clockwise angle.

If you want to convert the result from radians to degrees, multiply the result by 180/PI().

ATAN2 always returns a value between -3.14 and 3.14 radians (180 degrees).  Either x_number or y_number can be 0; however, if both arguments are 0, the function returns the error value #DIV/0!.

Examples:

ATAN2(1,1) equals .785 (π/4 radians)

ATAN2(-1,-1) equals -2.356 (-3π/4 radians)

ATAN2(-1,-1)*180/PI() equals -135 (degrees)

Related functions:

ATAN returns the arctangent of a number.

TAN returns the tangent of a number.

PI returns the value π.

Trigonometric Functions


AVERAGE()
AVERAGE(number1,[number2], . . .)

Purpose: The AVERAGE function returns the arithmetic mean, or average, of the arguments.

Arguments: number A number, formula that returns a number, or reference to a cell that contains either of these.

The AVERAGE function can have from 1 to 40 arguments.  Arguments that are text representations of a number, logical values, or empty cells are ignored, while arguments that are error values or text that cannot be translated into numbers cause errors.  In a reference argument, text, logical values, error values, and empty cells are ignored.

Results: The AVERAGE function returns a single value that is the arithmetic mean of the numbers specified by the arguments.

Use: The AVERAGE function is especially useful for calculating the average of the numbers in a range of cells.  Instead of writing a long formula to calculate the average for a number of values in a view, for example:

 =(B.'1+B.'2+B.'3+ B.'4+B.'5+.B'6+B.'7+B.'8+B.'9+B.'10)/10,

you can use a range argument and the AVERAGE function instead.

Examples: The sales figures for the three products in the Gadget Company's product line are displayed in the illustration below:

Sales.Pdt 1 equals1000

Sales.Pdt 2 equals750

Sales.Pdt 3 equals1250

Sales.Pdt 4 equals1200

To find the average sales across all product lines:

AVERAGE(Sales.Pdt 1:Sales.Pdt 4) equals 1050

AVERAGE(Sales.Pdt 1:Sales.Pdt 2, 1100) equals 950.

Related functions:

COUNT counts numbers.

COUNTA counts values.

SUM adds its arguments.

Statistical Functions


CAGR()
CAGR(value,rate,nper)

Purpose: The CAGR function calculates the value that results when a specified value is increased at the periodic rate, for the number of period specified by the arguments.

Arguments:value The initial value upon which the growth you want to calculate is based.

rate The growth rate that you want applied to value.

nper The number of periods you want the rate to be applied to value.  For example, if you want to calculate the effect of a specific rate on value over five years, nper would be 5.

Any of these arguments can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: CAGR returns the value which results when rate is applied to value for the specified nper.

Use: CAGR is useful for predicting future values based on a constant growth rate.

Examples: If Sales for Division A of the Gadget Company were $35,000 in 1989 and you want to know what they will be in 199, given a 10% compound growth rate per year, then:

CAGR(35000,10%,2) equals $42,350.

CAGR(100,7%,1) equals1070.

CAGR(100,6%,4) equals 126.25.

Related functions:

FV calculates the future value of an investment.

Financial Functions


CELL()
CELL(type_of_info,[reference])

Purpose: The CELL function returns information about the formatting, location, or contents of the specified cell.

Arguments: type_of_info A text value that specifies what type of information you want to know.

reference A reference to a cell.  When reference is omitted, it defaults to the current selection.  If reference is anything but a reference to a single cell, the error value #VALUE! is returned.

Results: CELL returns the following information about the cell specified by reference:

Type_of_info equals "COL":

Return value: If the specified cell is in a sheet, returns the column number of the cell in the sheet.

Type_of_info equals "ROW":

Return value: If the specified cell is in a sheet, returns the row number of the cell in the sheet.

Type_of_info equals "dimension_name" e.g., "Dim1", or "Financials", etc. :

Return value: If the specified cell is in a view, returns the index number of the object on the named dimension that references the cell.

Type_of_info equals "PROTECT":

Return value: If the specified cell is not locked, returns the logical value TRUE.  If the cell is locked, returns the logical value FALSE.

Type_of_info equals "ADRESS":

Return value: The reference of the specified cell, as text.

Type_of_info equals "CONTENTS":

Return value: The value contained in reference.

Type_of_info equals "FORMAT":

Return value: The format syntax of the format that is applied in the cell, as a string of text.  For example, if the format 0.00 is applied in the cell, "0.00" is returned.

Type_of_info equals "PREFIX":

Return value: A text value corresponding to the "label prefix" of the cell.  In CA-Compete!, this means that CELL returns:  " ' " if the cell contains left-aligned text; " ^ " if the cell contains centered text; " " " if the cell contains right-aligned text, and " ", the empty text, if the cell contains anything else.

Type_of_info equals "TYPE":

Return value: A text value corresponding to the type of data in the cell:  "b" for blank if the cell is empty; "l" for label if the cell contains a text constant; "v" for value if the cell contains anything else.

Type_of_info equals "GLOBAL":

Return value: The logical value TRUE if the cell contains a global formula, and the value FALSE if the cell contains anything else.

Examples:

 If the cell B.'4 is locked, then:

  CELL("protect",B.'4) equals TRUE

If the cell C.'10 has the format #,##0.00, then:

  CELL("format",C.'10) equals "#,##0.00"

If the cell D.'5 contains 25, then:

  CELL("contents",D.'5) equals 25.

Related functions:

TYPE returns a number indicating the data type of the contents of a cell.

SIZE returns the number of rows, columns, or objects in a range

Information Functions


CHAR()
CHAR(number)

Purpose: The CHAR function returns the ASCII character corresponding to the ASCII code number.

Arguments: number The ASCII code number that represents the character you want the function to return.

The code number can be any number between 1 and 255.

Results: The ASCII character that corresponds to the specified code number.

Examples:

CHAR(75) equals K

CHAR(37) equals %

Related functions:

CODE returns the ASCII code number that corresponds to a character.

Text Functions


CHOOSE()
CHOOSE(index_number,value1,[value2], . . .)

Purpose: The CHOOSE function uses an index number to choose from the list of items designated by the arguments.  If the index number is 1, the function will return the first item in the list; if the index number is 2 the function will return the second item; and so on.

Arguments: index_number The number of the item you want the function to look up.

value A value, number, text, or reference to a cell or vector (a range with a height, width, or depth of one cell) that contains elements of the list.

Results: If an argument is a multidimensional range or index_number is less than 1 or greater than the number of items in the list, CHOOSE returns the error value #VALUE!.

Unless CHOOSE is used to return a vector reference as an argument for another function that requests a vector or a range, CHOOSE returns the error value #VALUE! when an argument is a vector reference.

Use: The CHOOSE function lets you store a list of values, labels, cell references or vector references from which you can select an item by using an index number.  You cannot use CHOOSE, however, to retrieve a value, number, or text from a specific position in a range.

Examples: If you want to retrieve the second item from the list 5,10,20, then:

CHOOSE(2,5,10,20) equals 10.

You can also use the CHOOSE function to select text:

 CHOOSE(1,"Units","Price","Sales") equals "Units".

The CHOOSE function can also be used as an argument in another function:

 SUM(CHOOSE(2,10, B.'2:B.'5,"Units")) equals SUM(B.'2:B.'5).

However, if you include a vector reference as an argument when CHOOSE is not used in another function, then:

CHOOSE(2,10,30,B'2:B.'5,"Units") equals #VALUE!.

Related functions:

INDEX  uses an index to choose a reference from a range.

MATCH determines the position of the element in a vector that matches a specified value.

LOOKUP looks up values in a reference.

Lookup Functions


CLEAN()
CLEAN(text)

Purpose: The CLEAN function removes all non-printable characters from text.

Arguments: text A string of text, function that returns text, or reference to a cell that contains text.

Results: The same string of text specified in the argument but with the nonprintable characters eliminated.

 Examples: Since CHAR(7) returns a nonprintable character:

CLEAN(CHAR(7)&"text"&CHAR(7)) equals "text".

Related functions:

TRIM removes space from the specified text.

CODE returns the ASCII code number that corresponds to a character.

Text Functions


CODE()
CODE(text)

Purpose: Returns the ASCII code number that corresponds to the first character in the string of text specified by the argument.

Arguments: text A text value or reference to a cell that contains a text value.  If the argument is a character or string of text, it must be enclosed in double quotation marks.

Results: The ASCII code number that corresponds to the first character in text.

 Examples:

 CODE("A") equals 65

 CODE("Apple") equals 65

If the cell D.'4 contains the string of text "Telephones", then:

CODE(D.'4) equals 84.

Related functions:

CHAR returns the character that corresponds to an ASCII code number.

Text Functions


COS()
COS(radians)

Purpose: The COS function returns the cosine of an angle.

 Arguments: radians The number of radians in the angle whose cosine you want to calculate.  If your argument is in degrees, multiply the argument by PI()/180 to convert the argument to radians.

This argument can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: COS returns the cosine of radians, where radians is the number of radians in an angle.

Examples:

COS(1.5) equals 0.070737

COS(60*PI()/180) equals 0.5

Related functions:

ACOS returns the arccosine of a number.

PI returns the value π.

Trigonometric Functions


COST()
COST(known_y's,[known_x's],[new_x])

Purpose: The COST function uses the known x and y data points to calculate an exponential curve on a logarithmic scale and determines the new y-value that corresponds to the new x-value specified by the argument.

Arguments: known_y's A reference to a vector that contains the known y-values on the exponential curve.

known_x's A reference to a vector that contains the corresponding known x-values on the exponential curve.

new_x A number, text representation of a number, or reference to a cell that contains the x-value on the exponential curve which corresponds to the y-value you want to know.

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents the y-coordinate that corresponds to new_x on the exponential curve determined by the known_y's and the known_x's.

The COST function uses the formula y= b*(m1^(2^x)) to plot an exponential curve on a logarithmic scale.

If the argument known_x's is omitted, it defaults to a vector that contains the values 1, 2, 4, 8, 16, 32 . . . n, and is equal in size to the vector specified by known_y's.

If the argument new_x is omitted, it defaults to 1.  If any of the numbers in known_y's is negative, COST returns the error value #NUM!.

Use: The COST function has many uses in business planning, such as modeling economies of scale and experience/learning curves.  It is well known that increasing the scale operations–for example, by increasing the size of a plant or its number of operating hours–often lowers the cost of making each unit the plant produces.  Among plant engineers, there is also a well-known "1.8 rule" that states that doubling a plant's or machine's capacity only increases capital costs by a factor of 1.8, thus decreasing unit depreciation costs.  Experience and learning curves model productivity improvements that are driven by accumulated productive output (units produced during all time periods) or total hours of work put in.

The COST function can be used to describe any relationship in which a percentage change in the driving variable (e.g., plant capacity, advertising expenses, headcount, etc.) results in a percentage change in the driven variable (e.g, unit costs, selling costs, administrative costs, etc.).  It is used by the Tools Response Function command when you specify "log x log" as the scale for the curve you want to calculate.  You can use the COST function to model economic behavior that demonstrates a logarithmic relationship.  For example, it is applicable in manpower budgeting, sales forecasting, and feasibility or competitive analysis.

Examples: Departmental administrative costs are currently $10,000 per head per year; past performance suggests they will decrease by 10% whenever departmental headcount doubles (grows by 100%).  If headcount is expected to change in each of the next three years, what is the best way to estimate administrative costs per head in 1990, 1991, and 1992?  The most typical approach is a line-item by line-item bottoms-up estimate; this can entail a lot of work and is not always very accurate in the "out" years.  Alternatively, if you assume that administrative costs per head will remain constant, you will ignore the presence of fixed cost elements in the administrative cost structure, and will thus overstate the budget if headcount increases  Fast and surprisingly accurate forecasts can be derived by applying the cost function to historical and/or comparable values.

'1986.Admin Costs per Head equals $13,719

'1986.Headcount equals 25

'1987.Admin Costs per Head equals $12,345

'1987.Headcount equals 50

'1988.Admin Costs per Head equals $11,111.7

'1988.Headcount equals 100

'1989.Admin Costs per Head equals $10,000

'1989.Headcount equals 200

Using the model displayed above, if headcount in 1991 is expected to be 400 and administrative costs per head will be based on the firm's experience of costs and headcount from 1986 through 1989, then:

COST('1986.Admin Costs per Head:'1989.Admin Costs Per Head, '1986.Headcount:'1989.Headcount,400) equals $8999.48.

If you calculated administrative costs per head in 1990 without specifying known_x's (Headcount for 1986 through 1989) or new_x (Headcount in 1990), then:

COST('1986.Admin Costs per Head:Admin Costs Per Head, '1989) equals $15,242.91.

If Headcount is expected to be 600 in 1991 and administrative costs per head will be based on the firm's experience of costs and headcount from 1986 through 1989, then:

COST('1986.Admin Costs per Head:'1989.Admin Costs Per Head, '1986.Headcount:'1989.Headcount,800) equals $8099.29

If Headcount is expected to be 800 in 1992 and administrative costs per head will be based on the firm's experience of costs and headcount from 1986 through 1989, then:

COST('1986.Admin Costs per Head:

'1989.Admin Costs Per Head, '1986.Headcount:'1989.Headcount,800) equals $22,499.81.

Related function:

LOGLOGEST calculates an exponential curve on a logarithmic scale and returns the slope or  y-axis intercept of the curve.

GROWTH calculates an exponential curve on a semilogarithmic scale and returns a y-value on the line.

TREND calculates a regression line on a linear scale and returns a y-value on the line.

COST2 calculates a regression plane on a logarithmic scale and returns a new z-value on the plane.

SHARE calculates an S-curve and returns a new share-value on the curve.

Statistical Functions


COST2()
COST2(known_z's,known_y's,[known_x's],[new_y],[new_x])

Purpose: The COST2 function uses the known x, y, and z data points to calculate a regression plane on a logarithmic scale and determines the new z-value that corresponds to the pair of new  x- and y-values specified by the arguments.

Arguments: known_z's A reference to a vector that contains the known z-values on the plane.

known_y's A reference to a vector that contains the corresponding known y-values on the plane.

known_x's A reference to a vector that contains the corresponding known x-values on the plane.

new_y A number or reference to a cell that contains the x-value on the plane that corresponds to the z-value you want to know.

new_x A number or reference to a cell that contains the y-value on the plane that corresponds to the z-value you want to know.

The arguments known_z's, known_y's, and known_x's must all be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_z's, known_y's, and known_x's must contain the same number of cells.

Results: The number that represents the z-coordinate that corresponds to new_x and new_y  on the plane determined by the known_z's, known_y's, and known_x's.

The COST2 function uses the formula y= b*(m1^(2^x1))*(m2^(2^x2)) to plot an regression plane on a logarithmic scale.

If the argument known_x's is omitted, it defaults to a vector that contains the values 1, 2, 4, 8, 16 . . . n, and is equal in size to the vectors specified by known_z's and known_y's.

If the arguments new_y and new_x are omitted, they default to 1.  If any of the numbers in known_z's or known_y's is negative, COST2 returns the error value #NUM!.

Use: The COST2 function is similar to the COST function but allows you to evaluate costs that depend upon two driving variables (known_y's and known_x's) instead of one.  The COST2 function can be useful in business planning–for example, for modeling economies of scale and experience/learning curves.  It can be used to describe any relationship in which a percentage change in the two driving variables (e.g., machine utilization and machine rated capacity) results in a percentage change in the driven variable (e.g, unit costs).  It is used by the Tools Response Function command when you specify "log x log" as the scale for the curve you want to calculate given the known data points.  You can use the COST2 function to model economic behavior that demonstrates a logarithmic relationship and involves two driving variables..  The COST2 function is applicable in areas such as activity-based costing, manpower budgeting, sales forecasting, and feasibility or competitive analysis.

Examples:

 Per unit costs are currently $6.40 when the rated capacity of Machine 1 is 400 and the utilization is 80%.  Data on the performance of Machine 1 over the past three years is shown in the view displayed below.

'1987.Utilizationequals 20%

'1987.Rate Capacity equals 100

'1987.Per Unit Cost equals $40

'1988.Utilizationequals 20%

'1988.Rate Capacity equals 20

'1988.Per Unit Cost equals $32

'1989.Utilizationequals 40%

'1989.Rate Capacity equals 100

'1989.Per Unit Cost equals $20

Suppose plant engineers plan to purchase a second machine that has a rated capacity of 800 but is expected to perform in a similar fashion to Machine 1.  Utilization of the new machine in 1990, however, is expected to be only 65%.  If the plant engineers predict the per unit costs for items produced on the new machine in 1990 on the basis of the performance of Machine 1 over the past three years, then:

COST2('1987.Per Unit Cost:'1989.Per Unit Cost,'1987.Utilization: '1989.Utilization,'1987.Rated Capacity:'1989.Rated Capacity,65%,800) equals $6.86.

If the engineers calculate Per Unit Cost in 1990 without specifying new_y (Utilization in 1990) or new_x (Rated Capacity in 1990), then:

COST('1987.Per Unit Cost:'1989.Per Unit Cost,'1987.Utilization: '1989. Utilization,'1987.Rated Capacity:'1989.Rated Capacity) equals $9.68.

If Utilization of the new machine is expected to be 80%, and Rated Capacity to be 800 in 1991, then:

COST('1987.Per Unit Cost:'1989.Per Unit Cost,'1987.Utilization: '1989.Utilization,'1987.Rated Capacity:'1989.Rated Capacity,80%,800) equals $5.12.

Related function:

COST calculates an exponential curve on a logarithmic scale and returns a y-value on the curve.

TREND2 calculates a regression plane on a linear scale and returns a z-value on the plane.

GROWTH2 calculates a regression plane on a semilogarithmic scale and returns a z-value on the plane.

Statistical Functions


COUNT()
COUNT(value1, [value2], . . .)

Purpose: Counts the numbers in the list of arguments.

Arguments: value A number, logical value, text, or reference to a cell or range.

The COUNT function can have from 1 to 40 arguments.

Results: The COUNT function returns a single value.

Use: Counting how many cells in a given range contain numbers.  Arguments that are numbers, empty cells, logical values, or text representations of numbers (e.g., "two") are counted.  Arguments that are error values or text that cannot be translated into numbers are ignored.   If an argument is a reference, only cells in the reference that contain numbers are counted.  Empty cells, logical values, text, or error values within the reference are ignored.

Examples:

A.'1 equals 7

A.'2 equals TRUE

A.'3 equals blank

A.'4 equals "45"

A.'5 equals blank

A.'6 equals 89

A.'7 equals 89.8

A.'8 equals blank

A.'9 equals #VALUE!

COUNT(A.'6:A.'8) equals 2

COUNT(A'6:A'9) equals 2

COUNT(A.'1:A.'3) equals 1

COUNT(A.'1:A.'3,A.'4) equals 1

COUNT(A.'1:A.'10) equals 6

COUNT(0.1,FALSE(),"five",4,,3.333,200,,3,#DIV/0!) equals 8

Related functions:

COUNTA counts the values in its list of arguments.

AVERAGE averages its arguments.

SUM adds its arguments.

Statistical Functions


COUNTA()
COUNTA(value1, [value2], . . .)

Purpose: Counts the values in the list of arguments.

Arguments: value A number, logical value, text, or reference to a cell or range.

The COUNTA function can have from 1 to 40 arguments.

Results: The COUNTA function returns a single value.  Arguments that are numbers, empty cells, logical values, text representations of numbers (e.g., "two"), or error values are counted.

Use: Counting the number of cells in a range. that contain values.

Examples:

A.'1 equals 7

A.'2 equals TRUE

A.'3 equals blank

A.'4 equals "45"

A.'5 equals blank

A.'6 equals 89

A.'7 equals 89.8

A.'8 equals blank

A.'9 equals #VALUE!

A.'10 equals 10

COUNTA(A.'6:A.'8) equals 3

COUNTA(A'6:A'9) equals 4

COUNTA(A.'1:A.'3) equals 2

COUNTA(A.'1:A.'3,A.'4) equals 3

COUNTA(A.'1:A.'10) equals 8

COUNTA(1,,1) equals 3

Related functions:

COUNT counts the numbers in its list of arguments.

AVERAGE averages its arguments.

SUM adds its arguments.

Statistical Functions


DATE()
DATE(year,month,day)

Purpose: The DATE function returns the serial number of the date specified by the arguments.

Arguments: year A number that represents the year portion of the date.  The number for year should be between 1900 and 2078.  Values for year between 0 and 178 are interpreted as the years 1900 to 2078.

month A number that represents the month portion of the date.  The number for month should be between 1 and 12.

day A number that represent the day portion of the date.  The number for day should be between 1 and 31.

Each of these arguments can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: A value that represents the number of days that have elapsed between the date indicated in the arguments and the base date (January 1, 1900).  Using the Format Number command, you can format the cell that contains the DATE function so that the resulting serial number is expressed in a familiar date format.

The earliest date CA-Compete! can handle is January 1, 1900, and the latest date is December 31, 2078.  If you use arguments that define a date before the year 1900 or after the year 2078, the function will return the error value #NUM!.

Use: The DATE function is most useful in formulas where year, month, or day are formulas, not constants.

Since CA-Compete! will calculate the serial number from the date constant, it is not necessary, to use the DATE function within a formula that uses dates as constants.  If you use the text form of the date, "3/20/90", or "20-Mar-1990", CA-Compete! automatically converts the text to a serial number.

If you use negative numbers as arguments or an argument that does not make sense, DATE will return the error value #NUM!.

Examples:

DATE(90,1,1) equals 32874, the serial number that corresponds to the date January 1, 1990.

DATE(1900,12,31) equals 366

Related functions:

DATEVALUE returns the serial number of a date, but requires a text argument.

DAY converts a serial number to a day of the month.

WEEKDAY converts a serial number to a day of the week.

MONTH converts a serial number to a month of the year.

YEAR converts a serial number to a year.

NOW returns serial number corresponding to the current date and time.

Date and Time Functions


DATEVALUE()
DATEVALUE(date_text)

Purpose: The DATEVALUE function returns the serial number of the date specified by the text argument.

Arguments: date_text A date within the range January 1, 1900, and December 31, 2078.  Dates can be entered in any of the CA-Compete! built-in date formats, for example "2/15/90" or "15-Feb-90".  The argument must be enclosed in double quotation marks, since it is text.

Results: A value that represents the number of days that have elapsed between the date indicated in the arguments and the base date (January 1, 1900).  Using the Format Number command, you can format the cell that contains the DATEVALUE function so that the resulting serial number is expressed in a familiar date format.

The earliest date CA-Compete! can handle is January 1, 1900, and the latest date is December 31, 2078.  If you use arguments that define a date before the year 1900 or after the year 2078, the function will return the error value #NUM!.

The default if the year portion of date_text is omitted, is the current year from your computer's built-in clock.  If date_text contains any time information, it is ignored.

Examples:

DATEVALUE("4/16/90") equals 32979

DATEVALUE("16-Apr-90") equals 32979

DATEVALUE("31-Mar-90 12:00PM") equals DATEVALUE("31-Mar-90") equals 32963

If your computer's clock is set to the year 1990, then:

DATEVALUE("2/10") equals 32914

Related functions:

DATE returns the serial number of a date, but requires numeric arguments.

DAY converts a serial number to a day of the month.

WEEKDAY converts a serial number to a day of the week.

MONTH converts a serial number to a month of the year.

YEAR converts a serial number to a year.

NOW returns the serial number corresponding to the current date and time.

Date and Time Functions


DAY()
DAY(serial_number)

Purpose: The DAY function returns the day of the month that corresponds to a serial date number.

Arguments: serial_number A serial date value, a date function, or a reference to a cell that contains either a date function or a serial date value.

The argument can be given as text, such as "3/15/90" or "3-Mar-90", instead of a number.  CA-Compete! automatically converts the text to the corresponding serial date number.  A text argument must be enclosed in double quotation marks.

Results: The value of the day specified by the serial date value.

A serial date value is the date-time code used by CA-Compete! for date and time calculations.  Numbers range from 0 to 65380, corresponding to the dates January 1, 1900, through December 31, 2078.  Numbers to the right of the decimal point in a serial date number represent the time; numbers to the left represent the date.  For example, the date-time combination 12:00 p.m., January 1, 1901, is represented as 367.5.

If serial_number is a decimal number, DAY ignores the numbers to the right of the decimal point, which represent time.

Examples:

DAY(8) equals 8

DAY(8.6) equals 8

DAY("14-5-90") equals 14

The serial date for March 31, 1990, is 32904; therefore:

DAY(32904) equals 31.

If the cell B.'4 contains the function DATE(90,1,15), then

DAY(B.'4) equals 15.

Related functions:

WEEKDAY converts a serial number to a day of the week.

MONTH converts a serial number to a month of the year.

YEAR converts a serial number to a year.

HOUR converts a serial number to an hour of the day.

MINUTE converts a serial number to a minute.

SECOND converts a serial number to a second.

NOW returns the serial number corresponding to the current date and time.

Date and Time Functions


DDB()
DDB(cost,salvage,life,period)

Purpose: The DDB function determines the depreciation of an asset for a specific period of time using the double-declining balance method, given the asset's initial cost , its value at the end of its life, and the length of its useful life.

Arguments: cost The initial cost of the asset whose depreciation you are calculating.

salvage The value of the asset at the end of its life.

life The number of periods over which the asset is being depreciated, also called the useful life.

period The specific period for which you want to calculate the depreciation.

Each of these arguments can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.  All four arguments, however, must be positive numbers, and period and life must be given in the same units.  For example, if period is given in months, life must also be given in months.

Results: The value of the depreciation of the asset for the specified period.

The double-declining balance method calculates depreciation at an accelerated rate.  Depreciation is highest in the first period and decreases in successive periods.  Depreciation stops when the book value (cost minus the total depreciation over all prior periods) of the asset reaches the salvage value.

DDB uses the following formula to calculate depreciation for a given period:

(cost - total depreciation from prior periodes) * 2 / life.

Use: The DDB function uses the double-declining balance method to determine the depreciation of an asset for a given period of time in the life of that asset.

Examples: If a forklift purchased for your warehouse for $10,000 has a lifetime of 10 years and a salvage value of $1500, then:

DDB(10000,1500,3650,1) equals $5.48, the first day's depreciation.

DDB(10000,1500,120,1) equals $166.67, the first month's depreciation.

DDB(10000,1500,10,1) equals $2000.00, the first year's depreciation.

DDB(10000,1500,10,5) equals $819.20, the fifth year's depreciation.

DDB(10000,1500,10,10) equals $0.00, the tenth year's depreciation.

Related functions:

SLN returns the straight-line depreciation of an asset for one period.

SYD returns the sum-of-years' digits depreciation of an asset for a specified period.

Financial Functions


DIMENSION()
DIMENSION(dimension_name)

Purpose: The DIMENSION function uses the dimension name to return the index number of the dimension.

Arguments: dimension_name The name of the dimension for which you want to know the index number.  Since this argument is a text value, it must be enclosed in double quotation marks.

The DIMENSION function is not case-sensitive.

Results: The index number of the named dimension. The default dimensions are indexed in the following manner:  Dim1 is 1, Dim2 is 2, and Dim3 is 3.  If you rename these dimensions, they retain these index numbers.  All other dimensions in a model are indexed by order of creation.  For example, the fourth dimension added to the model will be indexed 4, the fifth dimension 5, and so on.  If you delete a dimension from the model, however, the index numbers of the remaining dimensions shift.  For instance, when you delete dimension 4 from a six-dimensional model, dimensions 5 and 6 will become dimensions 4 and 5 respectively.

Examples: In a model Dim1 has been renamed PRODUCTS, Dim2 FINANCIALS, and Dim3 YEARS and a fourth dimension was added and called REGIONS.  Therefore:

DIMENSION("FINANCIALS") equals 2.

DIMENSION("REGIONS") equals 4.

Related functions:

DIMENSIONNAME uses an index number to return the name of a dimension.

DIMENSIONS returns the number of dimensions in the model.

LABEL uses the dimension name and a cell reference to return the name of an object.

OBJECTINDEX  uses a dimension index to return the index number of an object.

OBJECTNAME uses dimension and object index numbers to return the name of an object.

Lookup Functions


DIMENSIONNAME()
DIMENSIONNAME(dimension_index)

Purpose:

 The DIMENSIONNAME function uses the dimension index number to return the name of the specified dimension as text.

Arguments: dimension_index A number that indicates one of the dimensions in the model.

The default dimensions are indexed in the following manner:  Dim1 is 1, Dim2 is 2, and Dim3 is 3.  If you rename these dimensions, they retain these index numbers.  All other dimensions in a model are indexed by order of creation.  For example, the fourth dimension added to the model will be indexed 4, the fifth dimension 5, and so on.  If you delete a dimension from the model, however, the index numbers of the remaining dimensions shift.  For instance, when you delete dimension 4 from a six-dimensional model, dimensions 5 and 6 will become dimensions 4 and 5 respectively.

Results: A dimension name as text.

If you use an index number for dimension_index that is greater than the number of dimensions in the model, DIMENSIONNAME returns the error value #VALUE!.

Examples:In a model  Dim1 has been renamed PRODUCTS, Dim2 FINANCIALS, and Dim3 YEARS.  A fourth dimension was added and called REGIONS.  Therefore:

DIMENSIONNAME(2) equals "FINANCIALS".

DIMENSIONNAME(4) equals "REGIONS".

Related Functions:

DIMENSION uses a dimension name to return the index number of a dimension.

DIMENSIONS returns the number of dimensions in the model.

LABEL uses the dimension name and a cell reference to return the name of an object.

OBJECTINDEX  uses a dimension index to return the index number of an object.

OBJECTNAME uses dimension and object index numbers to return the name of an object.

Lookup Functions


DIMENSIONS( )

Purpose: The DIMENSIONS function returns the number of dimensions in the model.

Arguments: Although the DIMENSIONS function has no arguments, you must include the empty parentheses with the function name.

Results: A number that represents the number of dimensions currently included in the model.

Examples:

If you are working in a three-dimensional model, then:

DIMENSIONS() equals 3.

If you are working in a five-dimensional model, then:

DIMENSIONS() equals 5.

Related functions:

DIMENSION uses a dimension name to return the index number of the dimension.

DIMENSIONNAME uses an index number to return the name of a dimension.

Lookup Functions


DLL()
DLL(Library,FunctionName,ArgumentTypes,ReturnType,Arguments)

Purpose: DLL calls the function given by FunctionName, in the DLL (Dynamic Link Library) specified by Library, with an argument list specified by ArgumentTypes and Arguments and returns a value, of type specified by ReturnType.

 Warning: The improper usage of this powerful function can cause various system crashes. It should be used only by advanced users, who have experience with DLLs.

Arguments:

Library Text, or reference to a cell that contains text, specifying the filename of the DLL.

FunctionName Text, or reference to a cell that contains text, specifying a function within the DLL.

ArgumentTypes: Text , or reference to a cell or range of cells containing text. Valid ArgumentTypes are the following:

BYTE, INT, WORD, LONG, DWORD, LONG, FLOAT, DOUBLE, STRING. Use 0 if there are no args.

ReturnType Text , or reference to a cell that contains text, describing the ReturnType. Valid ReturnTypes are the following:

BYTE, INT, WORD, LONG, DWORD, LONG, FLOAT, DOUBLE, STRING.

Results: Returns the ReturnValue of the DLL function. Be careful of side effects resulting from calling functions in dynamic libraries. If the Library or the function cannot be found a N/A error is returned. If the ArgumentTypes or the ReturnType are not valid, it returns a VALUE error.

Use: To call a function in a dynamic library (DLL).

Examples:

DLL("KERNEL.EXE","GetNumTasks",0,"INT",0) returns the number of tasks currently running in Windows.

A1 equals "WORD"

A2 equals "STRING"

A3 equals "STRING"

A4 equals "WORD"

B1 equals 0

B2 equals "Hello"

B3 equals "From DLL"

B4 equals 1

DLL("USER.EXE","MessageBox",A1:A4,"WORD",B1:B4) brings up a MesageBox, everytime the formula is evaluated, The TitleBar say "From DLL", the Message is "Hello". If you click 'Ok' it returns 1 - on "Cancel' it returns 2.

DLL("MYMATH.DLL","mySin","DOUBLE","DOUBLE",PI()/3) Assuming there is a DLL called MYMATH.DLL, which includes a function mySin, which get a double as argument and returns a double as well, this function is call and the result is its return value for the argument PI()/3.

Related functions:

EXTERN$ allows you to import data from other Windows applications and make absolute references to the data that are imported.

DDE and DLL Functions


DOLLAR()
DOLLAR(number,[decimals])

Purpose: The DOLLAR function rounds a number to a specified number of decimal places, formats it in currency format, and converts the result into text.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

decimals The number of decimal places to which you want to round number .  If decimals is negative, number is rounded to the left of the decimal point.  If decimals is omitted, the default is rounding the number to 2 decimal places.

Results: A number which is text and is formatted in the currency format defined under international settings in the Windows control panel (usually U.S. dollar currency).

Use: The DOLLAR function is useful when you want to format a number in the currency format to which your computer is set and also convert the number to text.  The currency formats are also available under the Format Number command, which will format a number in currency, but does not convert the number to text.  In contrast to a number formatted with the DOLLAR function, a number formatted with the Format Number command is still a number and not text.

Examples: If the currency defined under international settings in your Windows control panel is U.S. dollar and the format is set as $0.00, then:

DOLLAR(335.6197,1) equals "$335.60"

DOLLAR(335.6197,-1) equals "$340.00"

DOLLAR(-335.6197,-1) equals "($340.00)"

DOLLAR(335.6197) equals "$335.62"

If the cell A.'3 contains the number 1408.546, then:

DOLLAR(A.'3,0) equals"$1409.00"

 Related functions:

FIXED rounds a number, formats it in decimal format, and converts it to text.

TEXT converts a number to text.

VALUE converts text to a number.

Text Functions


EXACT()
EXACT(text1,text2)

Purpose: The EXACT function determines if the two arguments are exactly the same.

Arguments: text1 Text, a function that returns text, or a reference to a cell that contains text.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

text2 Text, a function that returns text, or a reference to a cell that contains text.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

Results: The logical value TRUE is returned if text1 and text2 are exactly the same.  If the two arguments are not identical (that is exactly the same characters including upper- and -lower case letters), the logical value FALSE is returned.

Examples:

EXACT("price","price") equals TRUE

EXACT("Price","price") equals FALSE

EXACT("price","pr ice") equals FALSE

If the cell A.'1 contains the text "Price", then:

EXACT(A.'1,"Price") equals TRUE.

If the cell A.'2 contains the text "$56.34", then:

EXACT(DOLLAR(56.341,2),A.'2) equals TRUE.

Related functions:

FIND finds one text value within another; this function is case-sensitive and does not accept wildcard characters.

LEN returns the length of a string of text.

SEARCH finds one text value within another; this function is not case-sensitive and accepts wildcard characters.

Text Functions


EXP()
EXP(number)

Purpose: The EXP function returns the value of the constant e (which is equal to 2.7182818), raised to the power specified by the argument.

Arguments: number A number that is the power to which you want to raise the constant e.  This argument can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: The value that results when the constant e is raised to the power specified by number.

Examples:

 EXP(1) equals 2.71828181 equals 2.7182818

 EXP(2) equals 2.71828182 equals 7.3890561

Since the EXP function is the inverse of the LN function, and the cell A.'1 contains the formula =LN(3), then:

EXP(A.'1) equals 3.

Related functions:

LN returns the natural logarithm of a number (inverse of EXP function).

LOG returns the logarithm of a number to a specified base.

Mathematical Functions


EXTERN()
EXTERN(application,topic,item,[x_index],[y_index]).

Purpose: The EXTERN function imports from other DDE compatible Windows applications the data specified by the arguments.

Arguments: application The name of the server application as it appears in the title bar of the application window.

topic The name of the topic (usually a file) as it appears in the title bar.

item The server application reference for the cell, range, value, or data field that designates the data within the topic you want to import into CA-Compete!.

x_index The y-coordinate of a specific data point in the item (range).  This is an optional argument which, if not specified, will default to 1.

y_index The x-coordinate of a specific data point in the item (range).  This is an optional argument which, if not specified, will default to 1.

The application, topic, and item arguments must be enclosed in double quotation marks.  The x_index and the y_index arguments indicate the specific point in the range in the server application where CA-Compete! should look to retrieve data.

Results: Data is retrieved from the specified location in the server application and entered in the CA-Compete! cell.  For example, for the following remote reference:

EXTERN("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",3,2)

CA-Compete! would retrieve the second item from the third column of the range R1C4:R4C4 which is located in the Excel spreadsheet called Sales.XLS, and display that value in the CA-Compete! cell that contains this remote reference.

 If the x_index and the y_index are not specified in the EXTERN function, CA-Compete! uses the coordinates 1,1 as a default.

Whenever you Edit Copy and Paste, Edit Fill Right, or Edit Fill Down to enter the EXTERN function you have created into other cells in your model, the x_index and the y_index will automatically update.  For example, if you Edit Copy the following remote reference:

EXTERN("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",1,1)

and then Edit Paste this reference one cell to the right, the EXTERN function will be updated so that the next item of data in the linked table is imported into your CA-Compete! model.  The remote reference pasted into the cell immediately the right of the cell that contains the formula above would be:

EXTERN("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",1,2)

If the remote reference had been pasted to the cell immediately below, then the remote reference would have been updated to the following instead:

 EXTERN("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",2,1).

Use: The EXTERN function is used to import data into your model from other CA-Compete! models and DDE compatible Microsoft Windows applications.

Examples: The range of CA-Compete! cells in a model that are linked to the result of an SQL Query to Q&E might contain the following remote references:

 EXTERN("QE","select * from EMP.DBF","all",1,1)

 EXTERN("QE","select * from EMP.DBF","all",1,2)

 EXTERN("QE","select * from EMP.DBF","all",1,3)

The three CA-Compete! cells that contain these remote references would each retrieve a value from the table produced as a result of the SQL query, "select * from EMP.DBF". The value displayed in each cell is determined by the x and y parameters defined by the EXTERN function.   These parameters represent the x and y coordinates of one of the values in the table that results from the SQL query.

From the Q&E file above, the query might yield the following table:

  Tyler  Bennett  E10297

  John  Rappl  E21437

  George   Woltman E00127

If the remote references listed above were entered into three CA-Compete! cells, these cells would disply the values Tyler, John and George respectively.

Related functions:

EXTERN$ allows you to import data from other Windows applications and make absolute references to the data that are imported.

DDE and DLL Functions


EXTERN$()
EXTERN$(application,topic,item,[x_index],[y_index]).

Purpose: The EXTERN$ function is an absolute version of the EXTERN function; it allows you to import data from other DDE compatible Windows applications and makes absolute references to the data specified by the arguments.

Arguments: application The name of the server application as it appears in the title bar of the application window.

topic The name of the topic (usually a file) as it appears in the title bar.

item The server application reference for the cell, range, value, or data field that designates the data within the topic you want to import into CA-Compete!.

x_index The y-coordinate of a specific data point in the item (range).  This is an optional argument which, if not specified, will default to 1.

y_index The x-coordinate of a specific data point in the item (range).  This is an optional argument which, if not specified, will default to 1.

The application, topic, and item arguments must be enclosed in double quotation marks.  The x_index and the y_index arguments indicate the specific point in the range in the server application where CA-Compete! should look to retrieve data.

Results: Data is retrieved from the specified location in the server application and entered in the CA-Compete! cell.  For example, for the following remote reference:

EXTERN$("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",2,3)

CA-Compete! would retrieve the second item from the third column of the range R1C4:R4C4 which is located in the Excel spreadsheet called Sales.XLS.

If the x_index and the y_index are not specified in the EXTERN$ function, CA-Compete! uses the coordinates 1,1 as defaults.

Whenever you Edit Copy and Paste, Edit Fill Right or Edit Fill Down to enter an EXTERN$ function into other cells in your model, the x_index and the y_index are absolute and do not update.  For example, if you Edit Copy the following remote reference:

 EXTERN$("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",1,1)

and then Edit Paste this reference one cell to the right, the coordinates specified by the x_index and the y_index would not change, because they are absolute.  The remote reference pasted into the cell immediately to the right of the cell that contains the formula above would be:

 EXTERN$("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",1,1)

and this cell would reference the same item of data in the supporting CA-Compete! model or Windows application that the copied EXTERN$ function does.  For example, if the remote reference had instead been pasted into the cell immediately below, the remote reference entered in that cell would still be:

  EXTERN$("Excel","C:\EXCEL\SALES.XLS","R1C4:R4C4",1,1).

Use: The EXTERN$ function is used to import data into your model from other CA-Compete! models and DDE compatible Microsoft Windows applications.  The EXTERN$ function is similar to an absolute reference in CA-Compete! and references a specific cell in the Windows application or CA-Compete! model you are using as a data source.  Since the EXTERN$ function is the absolute version of the EXTERN function, neither the x_index nor the y_index changes and the data point they specify in the source application does not vary when you cut or copy and paste this function to other cells in your CA-Compete! model.

Examples: The table of data in the Microsoft Excel file illustrated below is linked to a range of cells in a CA-Compete! model.

The cells in the linked CA-Compete! range contain the following formulas:

 EXTERN("EXCEL","C:\EXCEL\UNITS.XLS","B2:B5",1,1)

 EXTERN("EXCEL","C:\EXCEL\UNITS.XLS","B2:B5",2,1)

 EXTERN("EXCEL","C:\EXCEL\UNITS.XLS","B2:B5",3,1)

 EXTERN("EXCEL","C:\EXCEL\UNITS.XLS","B2:B5",4,1)

If you change the formula in the first cell of this CA-Compete! range to:

 EXTERN$("EXCEL","C:\EXCEL\UNITS.XLS","B2:B5",1,1)

and then copy and paste this formula to the three remaining cells in the range of linked data, all the cells in the CA-Compete! range will contain the following formula:

 EXTERN$("EXCEL","C:\EXCEL\UNITS.XLS","B2:B5",1,1)

The four CA-Compete! cells that contain this remote reference would each retrieve the same piece of data from the Excel worksheet that is linked to your CA-Compete! model. The data displayed in each cell is determined by the x and y parameters defined by the EXTERN$ function.   These parameters represent the x and y coordinates of one of the values in the Excel table–the value 10 from the cell B2 in the Excel worksheet illustrated above–and they do not change when the EXTERN$ function is cut or copied and pasted to other cells in your model.

Related functions:

EXTERN allows you to import data from other Windows applications and make relative references to the data that are imported.

DDE and DLL Functions


FACT()
FACT(number)

Purpose: The FACT function returns the factorial of the specified number.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: The factorial of number.  The factorial of number is equal to:

  (number)*(number-1)*(number-2)*. . .*(number-n).

where number-n = 1.  If number is not an integer, it is truncated.  If number is 0, FACT returns the number 1.  If number is negative, then FACT returns the error value #NUM!.

Examples:

FACT(1) equals 1

FACT(4) equals 4*3*2*1 equals 24

FACT(4.7) equals 24

FACT(0) equals 1

FACT(-1) equals #NUM

Related functions:

PRODUCT multiplies all of its arguments together.

Mathematical Functions


FALSE( )

Purpose: The FALSE function returns the logical value FALSE

Arguments: The FALSE function has no arguments.    You do not have to include the empty parentheses with the function name.

Results: With or without parentheses, FALSE returns the logical value FALSE.

Examples:

FALSE( ) equals FALSE

Related functions:

TRUE returns the logical value TRUE.

Logical Functions


FIND()
FIND(find_text,within_text,[start_at_num])

Purpose: The FIND function finds the specified text within another string of text.

Arguments: find_text The string of text you want to find within another string of text.  If find_text is "", CA-Compete! will match the first character that is searched–in other words, the character indicated by start_at_num.  This argument cannot contain any wildcard characters.

within_text The string of text in which you are searching for find_text.

start_at_num The number of the character in within_text where you want to begin searching for find_text.  The first character of within_text is number 1.   If start_at_num is omitted, the default is 1, and CA-Compete! will begin the search with the first character of within_text.  If start_at_num is greater than the length of within_text, then FIND returns the error value #VALUE!.

All arguments that are text strings must be enclosed in double quotation marks.

Results: A number that indicates the position of the first character in within_text that matches the text specified by find_text.

The FIND function is case-sensitive and will not match text unless the cases of each letter in find_text and within_text agree.  Both spaces and characters are counted.

Examples:

FIND("e","Average Price") equals 3

FIND("e","Average Price",4) equals 7

FIND("a","Average Price") equals 5

FIND("A","Average Price") equals 1

If the cell B.'10 contains the text "Average Price", then:

FIND("Price",B.'10) equals 9.

And, if the cell C.'1 contains the text "Price", then:

FIND(C.'1,B.'10) equals 9.

Related functions:

EXACT determines if two text values are identical.

LEN returns the length of a string of text.

SEARCH like FIND, finds one text value within another, but is not case-sensitive and accepts wildcard characters.

Text Functions


FIXED()
FIXED(number,[decimals])

Purpose: The FIXED function rounds a number to a specified number of decimal places, formats it in decimal format using a period and commas, and converts the result into text.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

decimals The number of decimal places to which you want to round number .  When decimals is negative, number is rounded to the left of the decimal point.  When decimals is positive, number is rounded to the right of the decimal point.  And, if decimals is omitted, number is rounded to two decimal places by default.

Results: A text representation of a number formatted in decimal format.

Use: The FIXED function is useful when you want to format a number in decimal format and also convert the number to text.  The decimal format available under the Format Number command formats a number in decimal format, but does not convert the number to text–a number formatted with the Format Number command is still a number.

Examples:

FIXED(335.6197,1) equals "335.6"

FIXED(335.6197,-1) equals "340"

FIXED(-335.6197,-1) equals "-340"

FIXED(335.6197) equals "335.62"

If the cell A.'3 contains the number 1408.546, then:

FIXED(A.'3,0) equals"1409"

Related functions:

DOLLAR rounds a number, formats it in currency format, and converts it to text.

TEXT converts a number to text.

VALUE converts text to a number.

Text Functions


FV()
FV(rate,nper,pmt,[pv],[type])

Purpose: The FV function calculates the future value of an investment based on periodic constant payments and a constant interest rate.

Arguments: rate The interest rate per period.

nper The total number of payment periods in the annuity.

pmt The periodic investment, or the payment made each period.

pv The present value, or the lump sum amount that the series of payments to be paid in the future is worth right now.

type A value that indicates to CA-Compete! whether the payments are received at the beginning (type is 1) or at the end (type is 0) of each period.

The arguments rate, nper, pmt, and pv can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: The future value of an investment, the opposite of present value (PV).

If the pv and type arguments are omitted, they default to 0.

For more information on the formula that FV uses to calculate the future value of an investment, see the topic Annuity Functions

Use: The FV function is used to transform a value today into an equivalent value in the future, given an assumed discount rate.

Examples:

FV(8%,10,-150,-500,1) equals 3426.29

FV(13%,24,-700) equals 17,955.12

For example, suppose you want to save money to take a vacation one year from now.  You deposit $500 into a savings account that earns 6% annual interest compounded monthly (monthly interest of 6%/12, or .5%) and you plan to deposit $100 at the beginning of every month for the next 12 months.  How much money will be in the account at the end of 12 months?

FV(0.5%,12,-100,-500,1) equals 1770.56

FV(5.25%,12,-200,,1) equals 3399.25

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

IPMT returns the interest payment for an investment for a given period.

NPER returns the number of periods (payments) for an investment.

PPMT returns the principal payment for an investment for a given period.

PMT returns the periodic total payment for an investment.

PV returns the present value of an investment.

RATE returns the interest rate per period of an investment.

Financial Functions


GROWTH()
GROWTH(known_y's,[known_x's],[new_x])

Purpose: The GROWTH function uses the known x and y data points to calculate an exponential curve on a semilogarithmic scale and determines the new y-value that corresponds to the new x-value specified by the arguments.

Arguments: known_y's A reference to the vector that contains the known y-values on the exponential curve.

known_x's A reference to the vector that contains the corresponding known x-values on the exponential curve.

new_x A number or reference to a cell that contains the value of the x-value on the exponential curve which corresponds to the y-value you want to know.

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents the y-coordinate on the straight line determined by the known_y's and the known_x's that corresponds to new_x.

The GROWTH function uses the formula y= b*(m^x) to plot an exponential curve on a semilogarithmic scale.

If the argument known_x's is omitted, it defaults to a vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vector specified by known_y's.  If the argument new_x is omitted, it defaults to 0.  If any of the numbers in known_y's is negative, GROWTH returns the error value #NUM!.

Use: The GROWTH function can be used to calculate trends and make near-term forecasts.  It is used by the Tools Forecast command when you choose "exponential" as the type of growth you want to forecast.  The GROWTH function calculates growth as a percentage rate rather than an absolute change.  When you use the GROWTH function to calculate a line that passes through your data, CA-Compete! assumes that an absolute change in the driving variable (e.g., years, price of raw materials, units produced, headcount, etc.) will result in a percentage change in the driven variable (e.g., sales, production costs, administrative costs, etc.).

Examples: During the second quarter of 1990, unit production costs at the Acme Widgit company decreased 10% for each additional 100 units of widgits that were produced.

Production.April equals 1000

Production.May equals 900

Production.June equals 810

Units.April equals 100

Units.May equals 200

Units.June equals 300

Using the data from above, if unit sales in July are expected to be 400 and you want to forecast production costs based on the preceding three months of data, then:

GROWTH(Production.April:Production.June,Units.April:Units.June,400) equals 729.

If you forecast production costs in May without specifying known_x's (Units sold in April through June) or new_x (Units sold in July), then:

GROWTH(Production.April:Production.June) equals 1111.11.

Related functions:

LILOGEST calculates an exponential curve on a semilogarithmic scale and returns the slope or the y-axis intercept of the curve.

TREND calculates a regression line on a linear scale and returns a y-value on the line.

COST calculates an exponential curve with a logarithmic versus logarithmic scale and returns a y-value on the curve.

GROWTH2 calculates a regression plane on a semilogarithmic scale and returns a new z-value on the plane.

SHARE calculates an S-curve and returns a new share-value on the curve.

Statistical Functions


GROWTH2()
GROWTH2(known_z's,known_y's,[known_x's],[new_y],[new_x])

Purpose: The GROWTH2 function uses the known x, y, and z data points to calculate a regression plane and determines the new z-value that corresponds to the pair of  new x- and y-values specified by the arguments.

Arguments: known_z's A reference to the vector that contains the known z-values on the plane

known_y's A reference to the vector that contains the corresponding known y-values on the plane.

known_x's A reference to the vector that contains the corresponding known x-values on the plane.

new_y A number or reference to a cell that contains the value of the x-coordinate on the plane which corresponds to the z-value you are seeking.

new_x A number or reference to a cell that contains the value of the y-coordinate on the plane which corresponds to the z-value you are seeking.

The arguments known_z's, known_y's, and known_x's must all be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_z's, known_y's, and known_x's must contain the same number of cells.

Results: The number that represents the z-coordinate that corresponds to new_x and new_y  on the plane determined by the known_z's, known_y's, and known_x's.

The GROWTH2 function uses the formula y= b*((m1^x1)*(m2^x2)) to plot a regression plane on a semilogarithmic scale.

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vectors specified by known_z's and known_y's.

If the arguments new_y and new_x are omitted, they default to 0.  If any of the numbers in known_z's or known_y's is negative, GROWTH2 returns the error value #NUM!.

Usage: The GROWTH2 function is similar to the GROWTH function but it allows you to calculate trends and make near-term forecasts that depend upon two driving variables (known_y's and known_x's) instead of one.  This function calculates growth as a percentage rate rather than an absolute change.  It can be used to describe any relationship in which an absolute change in the two driving variables results in a percentage change in the driven variable.

The GROWTH2 function is used by the Tools Response Function command when you select "Lin vs. Log scale" option for the double cost curve you want to calculate.  You can use the GROWTH2 function to model economic behavior that demonstrates a semilogarithmic relationship that involves two driving variables.  The GROWTH2 function is applicable in areas such as activity-based costing, manpower budgeting, sales forecasting, and feasibility or competitive analysis.

Examples: During the second quarter of 1990, unit production costs at the Acme Widgit company decreased 10% for each additional 100 units of widgits that were produced and for each $25 decrease in the cost of raw materials.

Production.April equals 1000

Production.May equals 900

Production.June equals 855

Materials.April equals 500

Materials.May equals 475

Materials.June equals 450

Units.April equals 100

Units.May equals 200

Units.June equals 300

Using the model displayed above, if unit sales in July are expected to be 400, material costs to decline to 425, and you want to forecast production costs based on the preceding three months of data, then:

GROWTH2(Production.April:Production.June,Units.April:Units.June, Materials.April:Materials.June,400,425) equals 783.493.

If you forecasted production costs in May based without specifying known_x's (Units sold in April through June), new_x (Units sold in July), or new_y (Materials in July), then:

GROWTH2(Production.April:Production.June) equals 1111.11.

Related functions:

GROWTH calculates an exponential curve on a semilogarithmic scale and returns a y-value on the line.

COST 2 calculates a regression plane on a logarithmic scale and returns a z-value on the plane.

TREND2 calculates a regression plane on a linear scale and returns a z-value on the plane.

Statistical Functions


HOUR()
HOUR(serial_number)

Purpose: The HOUR function returns the hour portion of a serial date/time number.

Arguments: serial_number A serial date/time value, a time function, or a reference to a cell that contains either a time function or a serial date/time value.

The argument can be given as text, such as "13:25:00" or "6:50:20 p.m.", instead of a number.  CA-Compete! automatically converts the text to the corresponding serial time number.  A text argument must be enclosed in double quotation marks.

Results: The value of the hour specified by the serial date/time value.  The hour is given as an integer, ranging from 0 (12:00 a.m.) to 23 (11:00 p.m.).

A serial time value is the date-time code used by CA-Compete! for date and time calculations.  Numbers range from 0 to 65380, corresponding to the dates January 1, 1900, through December 31, 2078.  Numbers to the right of the decimal point in a serial date number represent the time; numbers to the left represent the date.  For example, the date-time combination 12:00 p.m., January 1, 1901 is represented as 367.5.

Examples:

HOUR("4:05 AM") equals 4

HOUR(32904.8) equals19

HOUR uses the 24-hour time convention.  On the 24-hour clock, 4 a.m. is 4 o'clock and 4 p.m. is 16 o'clock, therefore

HOUR("3:00:40 PM") equals 15

If the cell B.'4 contains the function TIME(14,10,15), then

HOUR(B.'4) equals 14

Related functions:

WEEKDAY converts a serial number to a day of the week.

DAY converts a serial number to a day of the month.

MONTH converts a serial number to a month of the year.

YEAR converts a serial number to a year.

MINUTE converts a serial number to a minute.

SECOND converts a serial number to a second.

NOW returns the serial number corresponding to the current date and time.

Date and Time Functions


IF()
IF(conditional_test,value_if_true,[value_if_false])

Purpose: The IF function allows you to set up a conditional test that returns one value if the test is true and a second value if the test is false.

Arguments: conditional_test The conditional statement you want to test.

value_if_true The value you want returned if conditional_test is true.  This argument can be any value (text, number, formula, reference, etc.).

value_if_false The value you want returned if conditional_test is not true.   This argument can be any value (text, number, formula, reference, etc.).

.

Results: The value_if_true if the outcome of conditional_test is true, and the value_if_false is the outcome of  conditional_test is false.

If the argument value_if_false is omitted, it defaults to the logical value FALSE.

Usage: The IF function is useful for making conditional tests of cell values and formulas.  To construct more complex tests, you can nest up to eight additional IF functions as value_if_true and value_if_false.

Examples:

If cell A.'1 contains the value 14, then:

IF(A.'1>12,100,50) equals 100

If cell A.'1 contains the value 9, then:

IF(A.'1>12,100,50) equals 50

Related functions:

AND returns the logical value TRUE if all arguments are true.

OR returns the logical value TRUE if one or more arguments are true.

NOT returns the logical value that is the reverse of its argument.

Logical Functions


INDEX()
INDEX(ref,object_index1,[object_index2], . . .)

Purpose: The INDEX function returns the reference of the specified cell in a range.

Arguments: ref A reference to a range of cells in a view or sheet.   Ref can refer to a multidimensional range.

object_index The index number of an object that defines the cell you want to select from ref.  Object_index1 is the index number of the object on the first dimension in the model that defines the cell, object_index2 is the index number of the object on the second dimension in the model that defines the cell, and so on.  You must include an object_index for each of the dimensions in your model.  If an object_index is omitted, it defaults to 1.

If you are working on a sheet, object_index1 is the row number of the reference you want returned and object_index 2 is the column number.  For example, if index1 is 2, it refers to the second row in the sheet range A1:B3.  If index2 is 1, it refers to the first column in the sheet range A1:B3.  If object_ index2 is omitted, it defaults to 1.

Results: INDEX returns the reference of the specified cell.

If one of the index arguments refers to an object that does not define a cell in the range specified by ref, then INDEX returns the error value #REF!.

Use: The INDEX function lets you retrieve a value, number, or text from a specific position in a range.  You can store a list of values or labels in a view or sheet and then use the INDEX function to select an item from this range.

Examples: The model displayed below shows sales results by product for each sales representative in the Acme Widgit company.  Dim 1 has been renamed FINANCIALS, Dim 2 has been renamed TIME, Dim 3 has been renamed SALES REPS, and Dim 4 has been renamed PRODUCTS.  Sales is an object on the FINANCIALS dimension, and 1989 is an object on the TIME dimension. The objects of  PRODUCTS are Pdt 1, Pdt 2, Pdt 3, Pdt 4, and Total Pdt. The object of SALES REPS are Tom Richards, Mary Smythe, Jim Westin, Jerry Mulligan, Renee Jones, and Mike Harris:

INDEX(Pdt 1.Mary Smythe:Total Pdt.Mike Harris,1,1,2,1) equals a reference to Sales.'1989. Mary Smythe.Pdt 1

INDEX(Pdt 1.Mary Smythe:Total Pdt.Mike Harris,1,1,2) equal a reference Sales.'1989. Mary Smythe.Pdt 1

INDEX(Pdt 1.Mary Smythe:Total Pdt.Mike Harris,1,1,1,1) equals #REF!

INDEX(Pdt 1.Mary Smythe:Total Pdt.Mike Harris,1,1,2,4) equals a reference to Sales.'1989. Mary Smythe.Pdt 4

Related Functions:

CHOOSE uses an index number to choose a value from the list of items designated by the arguments.

LOOKUP finds the specified value in a lookup table and returns the result that corresponds to that value.

MATCH determines the position of the element in a vector that matches a specified value.

Lookup Functions


INT()
INT(number)

Purpose: The INT function rounds the number specified by the argument down to the nearest integer.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: An integer number with no decimal places.

Examples:

INT(5.7) equals 5

INT(-5.7) equals -6

If the cell A.'1 contains 35.6, then:

INT(A.'1) equals 35.

Related functions:

TRUNC truncates a number to an integer.

ROUND rounds a number to a specified number of digits.

MOD gives the remainder from division.

Mathematical Functions


IPMT()
IPMT(rate,per,nper,pv,[fv],[type])

Purpose: The IPMT function calculates the interest for a given period of an investment based on periodic constant payments and a constant interest rate.

Arguments: rate The interest rate per period.

per A number that specifies the period.  Per must be a number in the range 1 to nper.

nper The total number of payment periods in the annuity.

pv The present value, or the lump sum amount that the series of payments to be paid in the future is worth right now.

fv The future value, or a cash balance you want to be attained sometime in the future after the last payment is made.  If omitted, fv defaults to 0.

type A value that indicates to CA-Compete! whether the payments are received at the beginning (type is 1) or at the end (type is 0) of each period.  If omitted, type defaults to 0.

The rate, per, nper, pv, and fv arguments can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: The interest portion of the specified periodic payment on a loan or investment. See also topic Annuity Functions

Use: The IPMT function is used to find the interest portion of a loan over a particular period of time when the payment amounts and interest rate are known.

Examples: The following IPMT function finds the interest due in the fourth month of a five-year $10,000 loan at 13% annual interest:

IPMT(.13/12,4,60,10000) equals -104.42.

The interest due in the first year or the same loan if the payments are made yearly is:

IPMT(13%,1,5,10000) equals -1300.

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

NPER returns the number of periods (payments) for an investment.

PPMT returns the principal payment for an investment for a given period.

PMT returns the periodic total payment for an investment.

PV returns the present value of an investment.

RATE returns the interest rate per period of an investment.

Financial Functions


IRR()
IRR(values,[guess])

Purpose: The IRR function calculates the internal rate of return per period on an investment that generates an uneven stream of periodic payments.

Arguments: values A reference to a vector that contains numbers.  These numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods.  There must be at least one positive value and one negative value in order to calculate the internal rate of return.

guess A value that represents your estimate of what the rate will be.

IRR uses the order of values to interpret the order of cash flows.  Be sure to enter your payment and income values in the correct sequence.

Values must be a vector (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column or cylinder (a one-dimensional range that extends into the depth of the model) of data.  Text, logical, and empty cell values in the vector specified by values are ignored.

Results: The interest rate per period of an investment or loan.  The rate of return calculated by IRR is the interest rate corresponding to a "zero" net present value.

If values does not contain at least one positive value and one negative value, IRR returns the error value #DIV!.

IRR is calculated by iteration, and can have zero or more solutions,  If the successive results of IRR do not converge to within 0.0000001 after 20 iterations, IRR returns the error value #NUM!.

If IRR does not converge, try different values for guess.  IRR usually converges if given a guess between 0 and 1.

Use: The IRR function is useful for calculating the internal rate of return on an investment.  The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

IRR is similar to RATE but calculates the rate of return on an investment that generates uneven periodic cash flows rather than a constant periodic stream of cash.  IRR is to NPV what RATE is to PV.

Examples: Suppose your company is interested in beginning a new venture.  Start-up costs are estimated at $100,000, and the venture is expected to net the following income in the first five years: $15,000, $18,000, $25,000, $30,000, and $35,000.

To calculate the investment's rate of return after four years, use:

IRR(Income.'1990:Income.'1994) equals -0.0442 equals -4.42%.

To calculate the investment's internal rate of return after five years, use:

IRR(Income.'1990:Income.'1996) equals .0634 equals 6.34%.

To calculate the internal rate of return after two years, you will need to include a guess:

IRR(Income.'1990:Income.'1992,-20%) equals -0.4941 equals 49.41.

Related functions:

MIRR gives the internal rate of return where positive and negative cash flows are financed at different rates.

NPV returns the net present value of an investment based on uneven periodic cash flows.

RATE returns the interest rate for an investment based on constant periodic cash flows.

Financial Functions


ISBLANK()
ISBLANK(value)

Purpose:

 The ISBLANK function determines if a cell is empty.

Arguments: value A cell reference.

Results: The logical value TRUE is returned if the referenced cell is empty; otherwise the value FALSE is returned.

Usage: The ISBLANK function is useful for determining if data is missing in a cell.

Examples:

 If A.'1 contains 8 and  B.'3 is an empty cell then:

 ISBLANK(A.'1) equals FALSE.

 ISBLANK(B.'3) equals TRUE.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISCONSISTENT()
ISCONSISTENT(ref,[tolerance])

Purpose: The ISCONSISTENT function determines if all formulas that are available in the specified cell–both global and worksheet formulas–return the same value.

Arguments:	ref	A cell reference.

tolerance	A value that indicates the degree of difference that is acceptable among the values returned by the different formulas available in the cell.  If tolerance is omitted, it defaults to 0.

Results:	If the values returned by the different formulas available in the cell specified by ref differ by more than the value specified by tolerance then the logical value FALSE is returned; otherwise the value TRUE is returned.

For example, if tolerance is .01 and the values calculated by the formulas available in the specified cell differ by more than .01, then ISCONSISTENT returns the value FALSE.  If these values differ by less than .01, then ISCONSISTENT returns the value TRUE.

Use:	The ISCONSISTENT function is useful for determining if the different worksheet and global formulas available in a cell are all calculating the same value.  You can use this function to create a term that checks the consistency of calculated data throughout your model, as discussed under "Examples."

Examples:	The cell A.'3 contains a worksheet formula that returns the value 9.7 of the view.  Two other global formulas are available in this cell.  They both return the value 10.  If the functions listed below were entered in a cell A.'5, then:

ISCONSISTENT(A.'3) equals FALSE.

ISCONSISTENT(A.'3,1) equals TRUE.

You can also enter the ISCONSISTENT function in a cell as the worksheet formula and use it to evaluate the consistency of the global formulas available in that cell.  If you replaced the worksheet formula in cell A.'3 of the view illustrated above, then:

ISCONSISTENT(A.'3) equals TRUE,

since both of the global formulas available in the cell return the value 10.

To use the ISCONSISTENT function to create a term that checks the consistency of calculated data in your model, create a new term named "INCON" and define it as follows:

IF(ISCONSISTENT(X),X,"INCON")

so that any cell (X) that is consistent is displayed in NO TERM, and any cell that is not consistent displays the text string "INCON".

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISERR()
ISERR(value)

Purpose: The ISERR function determines if the argument is any CA-Compete! error value except #N/A.

Arguments: value A cell reference, or a function that returns a value.

Results: The logical value TRUE is returned if value is any CA-Compete! error value except #N/A (no value available); otherwise the value FALSE is returned.  The CA-Compete! error values are: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #CYC!, #STO!.

Use: The ISERR function is useful for determining if a cell contains any error value except the error value #N/A.

Examples:  If  A.'1 equals #N/A , C.'1 contains =B.'3/0 (#DIV/0) and B.'3 equals 56:

ISERR(C.'1) equals TRUE.

ISERR(B.'3) equals FALSE.

ISERR(A.'1) equals FALSE.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISERROR()
ISERROR(value)

Purpose: The ISERROR function determines if the argument is any CA-Compete! error value.

Arguments: value A cell reference, or a function that returns a value.

Results: The logical value TRUE is returned if value is any CA-Compete! error value, otherwise the value FALSE is returned.  The CA-Compete! error values are: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #CYC!, #STO!.

Use: The ISERROR function is useful for determining a cell contains any error value.

Examples: If  A.'1 equals #N/A , C.'1 contains =B.'3/0 (#DIV/0) and B.'3 equals 56:

ISERROR(C.'1) equals TRUE.

ISERROR(B.'3) equals FALSE.

ISERROR(A.'1) equals TRUE.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISLOGICAL()
ISLOGICAL(value)

Purpose: The ISLOGICAL function determines if the argument is a logical value.

Arguments: value A cell reference, or a function that returns a value.

Use: The ISLOGICAL function is useful for determining if a cell contains a logical value.

Results: The logical value TRUE is returned if value is a logical value; otherwise the value FALSE is returned.

Examples:

ISLOGICAL(TRUE()) equals TRUE.

ISLOGICAL(FALSE()) equals TRUE.

ISLOGICAL("TRUE()") equals FALSE.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISNA()
ISNA(value)

Purpose: The ISNA function determines if the argument is the error value #N/A (no value available).

Arguments: value A cell reference, or a function that returns a value.

Use: The ISNA function is useful for determining if a cell contains the error value #NA.

Results: The logical value TRUE is returned if value is the error value #N/A; otherwise the value FALSE is returned.

Examples:

ISNA(25) equals  FALSE.

ISNA(TRUE()) equals FALSE.

ISNA(NA()) equals TRUE.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

NA returns the errorcode #N/A.

Information Functions


ISNONTEXT()
ISNONTEXT(value)

Purpose: The ISNONTEXT function determines if the argument is not text.

Arguments: value A number, formula, text, or cell reference.

Results: The logical value TRUE is returned if value is not text; otherwise the value FALSE is returned.

Use: The ISNONTEXT function is useful for determining if a cell contains anything except text.

Examples:

ISNONTEXT(35) equals TRUE.

ISNONTEXT(TRUE()) equals TRUE.

ISNONTEXT("COMPETE") equals FALSE.

ISNONTEXT("price") equals FALSE.

Related functions:

 CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISNUMBER()
ISNUMBER(value)

Purpose: The ISNUMBER function determines if the argument is a number.

Arguments: value A number, formula, text, or cell reference.

Results: The logical value TRUE is returned if value is a number; otherwise the value FALSE is returned.

The value argument is not translated.  For example, the text value "11" is not translated to the number 11.

Use: The ISNUMBER function is useful for determining if a cell contains a number.

Examples:

ISNUMBER(56) equals TRUE.

ISNUMBER("COMPETE") equals FALSE.

ISNUMBER(PI()) equals TRUE.

ISNUMBER("5") equals FALSE.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISREF()
ISREF(value)

Purpose: The ISREF function  determines if the argument is a reference.

Arguments: value A number, text, formula, or cell reference.

Results: The logical value TRUE is returned if the argument is a cell reference; otherwise the value FALSE is returned.

Use: The ISREF function is useful for determining if a cell contains a reference.

Examples:

  ISREF(D.'2) equals TRUE.

  ISREF(19) equals FALSE.

  ISREF(TRUE()) equals FALSE.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


ISTEXT()
ISTEXT(value)

Purpose: The ISTEXT function determines if the argument is text.

Arguments: value A number, function, text, or cell reference.

Results: The logical value TRUE is returned if value is text; otherwise the value FALSE is returned.

Use: The ISTEXT function is useful for determining if a cell contains text.

Examples:

ISTEXT(2) equals FALSE

ISTEXT(TRUE()) equals FALSE

ISTEXT("COMPETE") equals TRUE

ISTEXT("price") equals TRUE

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of a value.

Information Functions


LABEL()
LABEL(dimension_name,[reference])

Purpose: The LABEL  function uses a dimension name and a cell reference to return the name of an object.

Arguments: dimension_name The name of the dimension on which the object whose name you want returned is located.  Since this argument is a text value, it must be enclosed in double quotation marks.

reference A reference to a cell that is defined by the object whose name you want returned.

Results: A text value that is the name of the object located on the dimension specified by dimension_name, and that is referenced in reference.  The LABEL function is not case-sensitive.

If reference is not specified, it defaults to the reference of the cell containing the LABEL function itself.

Use: The LABEL function can be used to return numeric object names (e.g., 1989, 1990, 1991, etc.) as text representations of numbers when you are predicting future values with functions such as TREND or GROWTH.

Examples: In a model  Dim 1 was renamed PRODUCTS, Dim 2 was renamed FINANCIALS, and Dim 3 was renamed YEARS.  There are four objects on the YEARS dimension: 1988, 1989, 1990, and 1991. The object of FINANCIALS are Units, Price, Sales, Total Costs, Net Operating Profits and the objects on PRODUCTS are Pdt 1, Pdt 2, Pdt 3 and Total Pdt:

LABEL("PRODUCTS",Units.Pdt3.1989) equals "Pdt 3"

LABEL("FINANCIALS",Units.Pdt 3.1989) equals "Units"

LABEL("YEARS",Units.Pdt3.1989) equals "1989"

Related functions:

OBJECTINDEX  uses a dimension index to return the index number of an object.

OBJECTNAME uses dimension and object index numbers to return the name of an object.

DIMENSION uses the dimension name to return the index number of a dimension.

DIMENSIONNAME uses an index number to return the name of a dimension.

Lookup Functions


LEFT()
LEFT(text,[number_of_characters])

Purpose: The LEFT function extracts the specified number of leftmost characters from a string of text.

Arguments: text A number, text, or a cell reference.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

number_of_chartacters The number of characters you want to extract from the beginning of the string of text specified by text.  This argument must be greater than zero.

Results: A string of text that is the length specified by number_of_characters and is extracted from the beginning of text.

If number_of_characters is greater than the length of text, then all of text is returned.  If number_of_characters is omitted, it defaults to 1.

Examples:

LEFT("Edwin Moore",5) equals "Edwin"

LEFT("Edwin Moore") equals "E"

If the cell C.'10 contains "large", then:

LEFT(C.'10) equals "l", and LEFT(C.'10,5) equals "large".

Related functions:

RIGHT extracts the specified number of rightmost characters from a string of text.

MID extracts a specified number characters from a string of text at a specific location in the string.

Text Functions


LEN()
LEN(text)

Purpose: The LEN function returns the number of characters in a string of text.

Arguments: text A number, text, or a cell reference.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

Results: A number that indicates the number of characters in the specified text.

Examples:

LEN("Units") equals 5

LEN(1200) equals (4)

If the cell B.'1 contains the text "Sales", then:

LEN(B.'1) equals 5.

If the cell Sales.Total Regions.1989 contains the formula SUM(Sales.North: Sales.West) and the result of this formula is 40978, then:

LEN(Sales.Total Regions.1989) equals 5.

Related functions:

EXACT determines if two text values are identical.

FIND finds one text value within another; this function is case-sensitive and does not accept wildcard characters.

SEARCH finds one text value within another; this function is not case-sensitive and accepts wildcard characters.

Text Functions


LINLINEST()
LINLINEST(known_y's,[known_x's],[type])

Purpose: The LINLINEST function uses known x and y data points to calculate a regression line on a linear scale and determine the slope or the y-axis intercept of the line.

Arguments: known_y's A reference to the vector that contains the known y-values on the line.

known_x's A reference to the vector that contains the corresponding known x-values on the line.

type A number that indicates whether you want the function to return the slope of the specified line (type is 0) or the y-axis intercept (type is 1).

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents either the slope (m in the formula y=mx + b) or the y-axis intercept (b in the formula y = mx + b) of the straight line determined by the y-values specified by known_y's and the x-values specified by known_x's.

The LINLINEST function uses the formula y= m1x1 + m2x2 + . . . + b to plot a regression line on a linear scale.

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vector specified by known_y's.  If the argument type is omitted, it defaults to 0, and LINLINEST returns the slope of the specified line.

The accuracy of the line calculated by LINLINEST depends on the degree of scattering in the data specified by the arguments.  The more linear the data, the more accurate the LINLINEST model.  LINLINEST uses the method of least squares for determining the best fit for the data.  The calculations for m is based on the following formula:

         n (Σxy)-(Σx)(Σy)

m = ------------------------

           n(Σ(x²))-(Σx)²

The calculation for b is based the following formula:

         (Σy)(Σ(x²))-(Σx)(Σxy)

b = -------------------------------

               n(Σ(x²))-(Σx)²

Use: The LINLINEST function can be used to calculate trends and then to determine the slope or y-axis intercept of the regression line that results.  The LINLINEST function calculates growth as an incremental change rather than a percentage rate.  When you use the LINLINEST function to calculate a line that passes through your data, CA-Compete! assumes that an absolute change in the driving variable (e.g., time, price of raw materials, units produced, headcount, etc.) will result in an absolute change in the driven variable (e.g., sales, production costs, administrative costs, etc.).

Examples:

Jan.Sales equals 10000

Feb.Sales equals 11000

Mar.Sales equals 12500

Apr.Sales equals 14045

Mai.Sales equals 13450

Jun.Sales equals 12305

Jan.Units equals 1000

Feb.Units equals 1195

Mar.Units equals 1300

Apr.Units equals 1475

Mai.Units equals 1400

Jun.Units equals 1285

LINLINEST calculates the regression line that passes through these data from the sales and units figures.  If you want to determine the slope value of this line, then:

LINLINEST(Jan.Sales:Jun.Sales,Jan.Units:Jun.Units,0) equals 8.92.

LINLINEST(Jan.Sales:Jun.Sales,Jan.Units:Jun.Units) equals 8.92.

If you want to determine the y-intercept value of this line, then:

LINLINEST(Jan.Sales:Jun.Sales,Jan.Units:Jun.Units,1) equals 832.09.

If you want to calculate the regression line that passes through the data based only on the sales figures, you do not have to specify known_x's because the x-values would then be the same as the default known_x's (e.g., 1, 2, 3, . . .).  The slope of this regression line would be given by:

LINLINEST(Jan.Sales:Jun.Sales,,0) equals 583.43.

The y-intercept of this regression line would be given by:

LINLINEST(Jan.Sales:Jun.Sales,,1) equals 10175.

Related function:

LINLOGEST calculates an exponential curve on a semilogarithmic scale and returns the slope or the y-axis intercept of the curve.

LOGLOGEST calculates an exponential curve on a logarithmic scale and returns the slope or  y-axis intercept of the curve.

TREND calculates a regression line on a linear scale and returns a y-value on the line.

Statistical Functions


LINLOGEST()
LINLOGEST(known_y's,[known_x's],[type])

Purpose: The LINLOGEST function uses known x and y data points to calculate an exponential curve on a semilogarithmic scale and determine the slope or y-axis intercept of the curve.

Arguments: known_y's A reference to the vector that contains the known y-values on the line.

known_x's A reference to the vector that contains the corresponding known x-values on the line.

type A number that indicates whether you want the function to return the slope of the specified line (type is 0) or the y-axis intercept (type is 1).

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents either the slope (m in the formula y = mx + b) or the y-axis intercept (b in the formula y = mx + b) of the exponential curve determined by the y-values specified by known_y's and the x-values specified by known_x's.

The LINLOGEST function uses the formula y=b*(m1^x1)*( m2^x2)) to plot an exponential curve on a semilogarithmic scale.

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vector specified by known_y's.  If type is omitted, it defaults to 0, and LINLOGEST returns the slope of the specified exponential curve.

If any of the numbers specified by the known_y's argument is negative, LINLOGEST returns the error value #NUM!.

The more closely the data specified by the arguments resembles an exponential curve, the better the curve calculated by LINLOGEST will fit your data.

Use: Like the LINLINEST function, the LINLOGEST function can be used to calculate a trend and then to determine the slope or y-axis intercept of the  curve that results.  Whereas the LINLINEST function fits a straight line to your data, however, the LINLOGEST function fits an exponential curve.  The LINLOGEST function calculates growth as a percentage rate rather than an incremental change.  When you use the LINLOGEST function to calculate a line that passes through your data, CA-Compete! assumes that an incremental change in the driving variable (e.g., time, price of raw materials, units produced, headcount, etc.) will result in a percentage change in the driven variable (e.g., sales, production costs, administrative costs, etc.).

Examples:

Jan.Sales equals 10000

Feb.Sales equals 11000

Mar.Sales equals 12500

Apr.Sales equals 14045

Mai.Sales equals 13450

Jun.Sales equals 12305

Jan.Units equals 1000

Feb.Units equals 1195

Mar.Units equals 1300

Apr.Units equals 1475

Mai.Units equals 1400

Jun.Units equals 1285

LINLOGEST calculates the exponential line that passes through these data from the sales and units figures.  If you want to determine the slope value of this line, then:

LINLOGEST(Jan.Sales:Jun.Sales,Jan.Units:Jun.Units,0) equals 1.0008.

LINLOGEST(Jan.Sales:Jun.Sales,Jan.Units:Jun.Units) equals 1.0008.

If you want to determine the y-intercept value of this line, then:

LINLOGEST(Jan.Sales:Jun.Sales,Jan.Units:Jun.Units,1) equals 4650.2.

If you want to calculate the exponential line that passes through this data based only on the sales figures, you do not have to specify known_x's because the x-values would then be the same as the default known_x's(e.g., 1, 2, 3, . . .).  The slope of this regression line would be given by:

LINLOGEST(Jan.Sales:Jun.Sales,,0) equals 1.0515.

The y-intercept of this regression line would be given by:

LINLOGEST(Jan.Sales:Jun.Sales,,1) equals 10181.

Related function:

LINLINEST calculates a regression line on a linear scale and returns the slope or y-axis intercept of the line.

LOGLOGEST calculates an exponential curve on a logarithmic scale and returns the slope or  y-axis intercept of the curve.

GROWTH calculates an exponential curve on a semilogarithmic scale and returns a y-value on the line.

Statistical Functions


LOGLOGEST()
LOGLOGEST(known_y's,[known_x's],[type])

Purpose: The LOGLOGEST function uses known x and y data points to calculate an exponential curve on a logarithmic scale and determines the slope or the y-axis intercept of the curve.

Arguments: known_y's A reference to the vector that contains the known y-values on the line.

known_x's A reference to the vector that contains the corresponding known x-values on the line.

type A number that indicates whether you want the function to return the slope of the specified line (type is 0) or the y-axis intercept (type is 1).

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents either the slope (m in the formula y = mx + b) or the y-axis intercept (b in the formula y = mx + b) of the logarithmic curve determined by the y-values specified by known_y's and the x-values specified by known_x's.

The LOGLOGEST function uses the formula y=b*(m1^(2^x1))*( m2^(2^x2)) to plot an exponential curve on a logarithmic scale.

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 4, 8, 16, 32 . . . n, and is equal in size to the vector specified by known_y's.  If type is omitted, it defaults to 0 and LOGLOGEST returns the slope of the specified logarithmic curve.

If any of the numbers specified by the known_y's argument is negative, LOGLOGEST returns the error value #NUM!.

The more closely the data specified by the arguments resembles a logarithmic curve, the better the line calculated by LOGLOGEST will fit your data.

Use: Like the LINLINEST or LINLOGEST functions, the LOGLOGEST function can be used to calculate a trend and then to determine the slope or y-axis intercept of the curve that results. The LOGLOGEST function, however, fits a different curve to your data.  Like the COST function, the LOGLOGEST function can be used to describe any relationship in which a percentage change in the driving variable (e.g., plant capacity, advertising expenses, headcount, etc.) results in a percentage change in the driven variable (e.g, unit costs, selling costs, administrative costs, etc.).  You can use the LOGLOGEST function to model economic behavior that demonstrates a logarithmic relationship.  The LOGLOGEST function is applicable in areas such as manpower budgeting, sales forecasting, and feasibility or competitive analysis.

Examples:

 Departmental administrative costs are currently $10,000 per head per year; history shows that they have grown by 50% whenever departmental headcount doubles (grows by 100%).

'1986.Admin Costs equals $2963

'1987.Admin Costs equals $4444.5

'1988.Admin Costs equals $6666.7

'1989.Admin Costs equals $10000

'1986.Headcount equals 25

'1987.Headcount equals 50

'1988.Headcount equals 100

'1989.Headcount equals 200

LOGLOGEST calculates the regression line that passes through these data based on the firm's experience of administrative costs per head and headcount.  If you want to determine the slope value of this line, then:

LOGLOGEST('1986.Admin Costs:'1989.Admin Costs,'1986.Headcount :'1989.Headcount,0) equals 1.499993.

LOGLOGEST('1986.Admin Costs Per Head:'1989.Admin Costs Per Head, '1986.Headcount: '1989.Headcount) equals 1.499993.

If you want to determine the y-intercept value of this line, then:

LOGLOGEST('1986.Admin Costs Per Head:'1989.Admin Costs Per Head, '1986.Headcount: '1989.Headcount,1) equals 450.8157.

If you want to calculate the regression line that passes through the data based only on the sales figures, you do not have to specify known_x's because the x-values will then be the same as the default known_x's (e.g., 1, 2, 3, . . .).  The slope of this regression line would be given by:

LOGLOGEST('1986.Admin Costs Per Head:'1989.Admin Costs Per Head) equals 1.499993.

Related function:

 LINLINEST calculates a regression line on a linear scale and returns the slope or the y-axis intercept of the line.

LINLOGEST calculates an exponential curve on a semilogarithmic scale and returns the slope or the y-axis intercept of the curve.

COST calculates an exponential curve on a logarithmic scale and returns a y-value on the line.

Statistical Functions


LINLINS()
INLINS(known_y's,[known_x's],[new_x])

Purpose: The LINLINS function calculates a straight line between each successive pair of known data points on a linear scale and determines a new y-value for the new x-value specified by the argument.

Arguments: known_y's A reference to the vector that contains the known y-values.

known_x's A reference to the vector that contains the corresponding known x-values.

new_x A number or reference to a cell that contains the  x-value that corresponds to the y-value you are seeking.

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data, however; the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents the y-coordinate that corresponds to new_x on the curve determined by the known_y's and the known_x's.

The LINLINS function uses the formula y=y[i]+(y[i+1]-y[i])*((x-x[i])/(x[i+1]-x[i])) to plot a line between each pair of data points on a linear scale.

if new_x < x1  LINLINS returns y0

if x1 < new_x < x2  LINLINS returns y0+(y1 - y0)*(x - x1)/(x2 - x1)

if x2 < new_x < x3  LINLINS returns y1+(y2 - y1)*(x - x2)/(x3 - x2)

if new_x  > xn  LINLINS returns yn

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vector specified by known_y's.  If the argument new_x is omitted, it defaults to 0.  If any of the numbers in known_y's is negative, LINLINS returns the error value #NUM!.

Usage: The LINLINS function is useful for smoothing out staircase-step relationships to better approximate a curve and to model economic behavior more realistically.  The LINLINS function calculates growth as an incremental change rather than a percentage rate.  When you use the LINLINS function to calculate a line between each pair of known data points, CA-Compete! assumes that an absolute change in the driving variable (e.g., time, price of raw materials, units produced, headcount, etc.) will result in an absolute change in the driven variable (e.g., sales, production costs, administrative costs, etc.).  For example, if the year-end headcount is known, or can be projected, the LINLINS function allows the headcount for each month to be interpolated in linear increments (12 in this case) that are consistent with an arbitrary, user-defined range of specified year-end figures.

The essential advantage of all the CA-Compete! "S" functions (LINLINS, LINLOGS, and LOGLOGS) is that they provide an easy approximation of complex mathematical relationships.  During the budgeting and planning processes, managers often must build what-if models for product life cycles, project headcount projections, and other economic relationships that have an up-then-down pattern.  Since the mathematical relationships that model this kind of behavior are too complex for most users to define, static, less realistic assumptions are typically used.  Sensitivity analysis is then impossible or must be performed manually.  With CA-Compete!, however, you can specify the known points and then use the "S" function that best describes the expected trend to plot the appropriate line between each pair of known data points.

Examples: R&D costs and headcount at Big Idea Labs for the past four years are shown in the view displayed below.

'1986.R&D Costs equals $1.0Mio

'1987.R&D Costs equals $1.4Mio

'1988.R&D Costs equals $2.0Mio

'1989.R&D Costs equals $2.2Mio

'1986.R&D Headcount equals 100 people

'1987.R&D Headcount equals 200 people

'1988.R&D Headcount equals 300 people

'1989.R&D Headcount equals 400 people

If R&D headcount is expected to be reduced to 350 in 1990 and you want to forecast R&D costs in 1990, then:

LINLINS('1986.R&D Costs:'1989.R&D Costs,'1986.R&D Headcount: '1989.R&D Headcount,350) equals 2.1E06 equals $2.1 million.

If R&D headcount is expected to be reduced to 290 in 1991 and you want to forecast R&D costs in 1991, then:

LINLINS('1986.R&D Costs:'1989.R&D Costs,'1986.R&D Headcount: '1989.R&D Headcount,290) equals 1.9E06 equals $1.9 million.

Related function:

LINLOGS calculates an exponential curve between each pair of known data points on a semilogarithmic scale returns a new y-value on the curve.

LOGLOGS calculates an exponential curve between each pair of known data points on a logarithmic scale and returns a y-value on the curve.

Lookup Functions


LINLOGS()
LINLOGS(known_y's,[known_x's],[new_x])

Purpose: The LINLOGS function calculates an exponential curve between each pair of known data points on a semilogarithmic scale and determines a new y-value for the new x-value specified by the argument.

Arguments: known_y's A reference to the vector that contains the known y-values.

known_x's A reference to the vector that contains the corresponding known x-values.

new_x A number or reference to a cell that contains the  x-value that corresponds to the y-value you want to know.

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents the y-coordinate on the exponential curve determined by the known_y's and the known_x's that corresponds to new_x.

The LINLOGS function uses the formula y=y0*(y1/y0)^((x - x0)/(x1 - x0))  to plot an exponential curve on a semilogarithmic scale.

The LINLOGS function uses the formula y=y[i]*(y[i+1]/y[i])^((x-x[i])/(x[i+1]-x[i])) to plot a line between each pair of data points on a linear scale.

if new_x < x1  LINLOGS returns y0

if x1 < new_x < x2  LINLOGS returns y0*(y1 / y0)^(x - x1)/(x2 - x1)

if x2 < new_x < x3  LINLOGS returns y1*(y2 / y1)^(x - x2)/(x3 - x2)

if new_x  > xn  LINLOGS returns yn

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vector specified by known_y's.  If the argument new_x is omitted, it defaults to 0.  If any of the numbers in known_y's is negative, LINLOGS returns the error value #NUM!.

Use: The LINLOGS function is useful for smoothing out staircase-step relationships to better approximate a curve and to model economic behavior more realistically.  Like the LINLINS function, the LINLOGS function fits a curve between the known data points.  But whereas the LINLINS function plots a straight line between each of these points, the LINLOGS function plots an exponential curve.  Also, LINLOGS calculates growth as an percentage rate rather than an incremental change.  When you use this function to calculate a curve between two of the known data points, CA-Compete! assumes that an absolute change in the driving variable (e.g., time, price of raw materials, units produced, headcount, etc.) will result in a percentage change in the driven variable (e.g., sales, production costs, administrative costs, etc.).  For example, if the year-end headcount is known, or can be projected, the LINLOGS function allows the headcount for each month to be interpolated in linear increments (12 in this case) that are consistent with an arbitrary, user-defined range of specified year-end figures.

The essential advantage of all the CA-Compete! "S" functions (LINLINS, LINLOGS, and LOGLOGS) is that they provide an easy approximation of complex mathematical relationships.  During the budgeting and planning processes, managers often must build what-if models for product life cycles, project headcount projections, and other economic relationships that also have an up-then-down pattern.  Since the mathematical relationships that model this kind of behavior are too complex for most users to define, static, less realistic assumptions are typically used.  Sensitivity analysis is then impossible or must be performed manually. With CA-Compete!, however, you can specify the known points and then use the "S" function that best describes the expected trend to plot the appropriate line between each pair of known data points.

Examples: In sales force management, it is commonly believed that results vary with sales "push" (i.e., changes in salesperson coverage).  Experience at the Acme Widgits company shows that there is a diminishing benefit to increased levels of push.  For example, the view displayed below shows the frequency of calls and the average order size by region for one sales team at Acme Widgits:

North.Weeks Btw Calls equals 6

South.Weeks Btw Calls equals 8

East.Weeks Btw Calls equals  10

West.Weeks Btw Calls equals 12

North.Avg Order Size equals 1408.73

South.Avg Order Size equals 1277.76

East.Avg Order Size equals  1056

West.Avg Order Size equals 800

If the sales manager at Acme Widgits wants to know what the average order size would be if Team 1 called on customers every nine weeks, then:

LINLOGS(North.Avg Order Size:West.Avg Order Size, North.Weeks Btw Calls:West.Weeks Btw Calls,9) equals 1161.6.

If the sales manager wants to know what average order sizes would be if Team 1 called on customers every seven weeks, then:

LINLOGS(North.Avg Order Size:West.Avg Order Size, North.Weeks Btw Calls:West.Weeks Btw Calls,7) equals 1341.648.

Related function:

The LINLINS function calculates a line between each pair of known data points on a linear scale and returns a new y-value on the line.

LOGLOGS calculates an exponential curve between each pair of known data points on a logarithmic scale and returns a y-value on the curve.

Lookup Functions


LN()
LN(number)

Purpose: The LN function returns the natural logarithm of the number specified by the argument.  Natural logarithms are the logarithms based on the constant e (which is equal to 2.7182818).

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.  Number must be positive.

Results: The number that represents the power to which the constant e must be raised to equal number.

Use: LN is the inverse of the EXP function, e raised to the power specified by the argument number.

Examples:

LN(2.7182818) equals 1

LN(7.3890561) equals2

LN(54) equals 3.988984

Since the LN function is the inverse of the EXP function, if the cell A.'1 contains the formula =EXP(3), then:

LN(A.'1) equals 3.

The LN function used by itself will not accept a range reference as an argument; for example:

LN(A.'1:C.'1) equals #VALUE!.

However, you can use a range reference as an argument to the LN function if the function is used as an argument to another function that accepts range references as arguments, such as:

SUM(LN(A.'1:C.'1)) equals SUM(LN(A.'1),LN(B.'1),LN(C.'1)).

Related function:

EXP  returns the value of the constant  e (which is equal to 2.7182818), raised to the specified power.

LOG  returns the logarithm of a number to a specified base.

Mathematical Functions


LOG()
LOG(number,[base])

Purpose: The LOG function calculates the logarithm of a number to a specified base.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.  Number must be positive.

base A number that represents the base you want to use to calculate the logarithm of number.  If base is omitted, it defaults to 10.

Results: The number that represents the power to which base must be raised to equal number.

Examples:

LOG(10) equals 1

LOG(4,2) equals 2

LOG(54,2.7182818) equals 3.988984

Related function:

The EXP function returns the value of the constant  e (which is equal to 2.7182818), raised to the power specified by the function's argument.

The LN function returns the natural logarithm of a number (the inverse of EXP).

LOG10 returns the base 10 logarithm of a number.

Mathematical Functions


LOG10()
LOG10(number)

Purpose: The LOG10 function calculates the base 10 logarithm of a number.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.  Number must be positive.

Results: The number that represents the power to which 10 must be raised to equal number.

Examples:

LOG10(10) equals 1

LOG10(100) equals 2

LOG10(4) equals .060206

Related function:

EXP returns the value of the constant  e (which is equal to 2.7182818), raised to the specified power.

The LN function returns the natural logarithm of a number (the inverse of EXP).

LOG returns the logarithm of a number to a specified base.

Mathematical Functions


LOGLOGS()
LOGLOGS(known_y's,[known_x's],[new_x])

Purpose: The LOGLOGS function calculates an exponential curve between each pair of known data points on a logarithmic curve and determines a new y-value for the new x-value specified by the argument.

Arguments: known_y's A reference to the vector that contains the known y-values.

known_x's A reference to the vector that contains the corresponding known x-values.

new_x A number or reference to a cell that contains the  x-value that corresponds to the y-value you are seeking.

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data, however; the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents the y-coordinate on the exponential curve determined by the known_y's and the known_x's that corresponds to new_x.

The LOGLOGS function uses the formula y=y[i]*(y[i+1]/y[i])^((log2(x/x[i]))/(log2(x[i+1]/x[i]))) to plot a line between each pair of data points on a linear scale.

if new_x < x1  LOGLOGS returns y0

if x1 < new_x < x2  LOGLOGS returns: y0*(y1 / y0)^(log2(x / x1))/(log(x2 / x1))

if x2 < new_x < x3  LOGLOGS returns: y1*(y2 / y1)^(log2(x / x2))/(log(x3 / x2))

if new_x  > xn  LINLOGS returns yn

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 4, 8, 16, 32 . . . n, and is equal in size to the vector specified by known_y's.  If the argument new_x is omitted, it defaults to 0.  If any of the numbers in known_y's is negative, LOGLOGS returns the error value #NUM!.

Use: The LOGLOGS function is useful for smoothing out staircase-step relationships to better approximate a curve and to model economic behavior more realistically.  Like the LINLINS and LINLOGS functions, LOGLOGS fits a curve between the known data points.  The LOGLOGS function, however, fits a different to curve your data.  Like the COST function, it can be used to describe any relationship in which a percentage change in the driving variable (e.g., plant capacity, advertising expenses, headcount, etc.) results in a percentage change in the driven variable (e.g, unit costs, selling costs, administrative costs, etc.).  You can use the LOGLOGS function to set up a series of separate cost functions that apply only within certain boundaries.  For example, unit costs might be expected to decline by 20% as plant capacity doubled, but stop declining beyond the level of capacity at which component duplication begins to occur.

The essential advantage of all the CA-Compete! "S" functions (LINLINS, LINLOGS, and LOGLOGS) is that they provide an easy approximation of complex mathematical relationships.  During the budgeting and planning processes, managers often must build what-if models for product life cycles, project headcount projections, and other economic relationships that also have an up-then-down pattern.  Since the mathematical relationships that model this kind of behavior are too complex for most users to define, static, less realistic assumptions are typically used.  Sensitivity analysis is then impossible or must be performed manually.  With CA-Compete!, however, you can specify the known points and then use the "S" function that best describes the expected trend to plot the appropriate line between each two of known data points.

Examples: The Acme Widgits company finished building a second plant (Plant 2) in 1987 and plans to double capacity at the plant in 1992.  Because of the amortization of start-up and capital costs, plant managers expect the unit cost of widgits produced at the plant to be 30% less in 1992.  Plant capacity will again be doubled in 1997. That doubling, however, is expected to reduce unit costs by only 10%.  After 1997, any additional increase in plant capacity is not expected to reduce unit costs further since the remaining costs will essentially be variable material costs.  The view displayed below shows data for unit costs as a function of plant capacity at Plant B of the Acme Widgits company from 1987 to 2002.

1987.Unit Cost equals $2.42

1992.Unit Cost equals $1.69

1997.Unit Cost equals $1.52

2002.Unit Cost equals $1.52

1987.Capacaty equals 240

1992.Capacaty equals 480

1997.Capacaty equals 960

2002.Capacaty equals 1600

If  you want to calculate the unit cost of widgits produced at Plant B in 1990, when capacity is expected to be 360, then:

LOGLOGS('1987.Unit Cost:'2002.Unit Cost,'1987.Capacity:'2002.Capacity,360) equals $1.96.

If capacity at the plant is expected to be 570 in 1993, then:

LOGLOGS('1987.Unit Cost:'2002.Unit Cost,'1987.Capacity:'2002.Capacity,570) equals $1.65.

If capacity at the plant is expected to be 1200 in 1997, then:

LOGLOGS('1987.Unit Cost:'2002.Unit Cost,'1987.Capacity:'2002.Capacity, 1200) equals $1.52

Related function:

LINLINS calculates a line between each pair of known data points on a linear scale and returns a new y-value on the line.

LINLOGS calculates an exponential curve between each pair of known data points on a semilogarithmic scale and returns a new y-value on the curve.

Lookup Functions


LOOKUP()
LOOKUP(result_vector,lookup_vector,lookup_value)

Purpose: The LOOKUP function finds the specified value in a lookup table and returns the result that corresponds to that value.

Arguments: result_vector A reference to the vector (a one-dimensional range that has a height, width, or depth of one cell) that contains the results that correspond to each value in lookup_vector.

lookup_vector A reference to the vector (a one-dimensional range that has a height, width, or depth of one cell) that contains the values where you want to search for lookup_value.

lookup_value The value you want to find in the lookup table.  This argument can be a number, text, or logical value.

The values in result_vector and lookup_vector can be numbers, text, or logical values.  The values in lookup_value must be arranged in ascending order–for example, -2, -1, 0, 1, 2, 3, . . . , A-Z, or FALSE and then TRUE–otherwise LOOKUP may not return the correct value from result_vector.  Upper- and lower-case text are equivalent.

Results: The value in result_vector that corresponds to the value in lookup_vector that matches lookup_value.  If LOOKUP cannot find lookup_value in the range of values defined by lookup_vector, it uses the largest value in lookup_vector that is less than or equal to lookup_value.  LOOKUP returns the value in result_vector that corresponds to this value.

Use: You can use LOOKUP to retrieve a value, number, or text from a table of results.

Examples:

A.2 to A.5 equals  1.1,2.1,3.1,4.1

B.2 to B.5 equals  40000,35000,15000,8000

C.2 to C.5 equals  Luxury, sport, utility, and basic transport.

LOOKUP(B.'2:B.'5,A.'2:A.'5,2.1) equals 35000.

LOOKUP(B.'2:B.'5,A.'2:A.'5,2.5) equals 35000.

LOOKUP(C.'2:C.'5,A.'2:A.'5,5) equals "basic transport"

LOOKUP(C.'2:C.'5,A.'2:A.'5,1) equals #N/A! since 0001 is less than the smallest value in the lookup_vector A.'2:A.'5.

Related functions:

CHOOSE uses an index number to choose a value from the list of items designated by the arguments.

INDEX uses an index number to choose a value from a reference.

MATCH determines the position of the element in a vector that matches a specified value.

Information Functions


LOWER()
LOWER(text)

Purpose: The LOWER function converts all upper-case letters in a specified string of text to lower-case letters.  Characters in the string of text that are not letters are unchanged.

Arguments: text A number, text, or cell reference.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

Results: The same string of text as is specified by text but with lower-case letters in the place of the upper-case letters in text .

Examples:

LOWER("B") equals "b"

LOWER("Price") equals "price"

If the cell A.'10 contains the text "TYPE 5", then:

LOWER(A.'10) equals "type 5".

Related functions:

PROPER capitalizes the first letter in each word in the specified string of text.

UPPER capitalizes every letter in the specified string of text.

Text Functions


MATCH()
MATCH(lookup_value,lookup_vector,[type_of_match])

Purpose: The MATCH function finds the value in a vector that matches the specified value, according to a type of match, and returns the position within the range of that matching value.

Arguments: lookup_value The value you want to match.  This argument can be a number, text, or logical value.

lookup_vector A reference to the vector (a one-dimensional range that has a height, width, or depth of one cell) that contains the values where you want to find a match for lookup_value.  This vector can contain numbers, text, or logical values.

type_of_match A number that indicates the type of match you want between lookup_value and one of the values in lookup_vector.

Results: A number that indicates the position within lookup_vector of the value that matches lookup_value according to type_of_match.

Type_of_match can be any of the following:

If Type_of_match is 1 or omitted MATCH finds 'The largest value that is less than or equal to lookup_value' In this case the Lookup_vector must  be arranged in ascending order:  . . . -2, -1, 0, 1, 2, . . . A-Z, FALSE, TRUE.

If Type_of_match is -1 MATCH finds 'The smallest value that is greater than or equal to  lookup_value' In this case the Lookup_vector must  be arranged in ascending order:  . . . -2, -1, 0, 1, 2, . . . A-Z, FALSE, TRUE.

If Type_of_match is 0 MATCH finds 'The first value that is exactly equal to  lookup_value' In this case the Lookup_vector can be in any order.

Since MATCH is not case-sensitive, upper- and lower-case text are equivalent.  If MATCH cannot find lookup_value in the range of values defined by lookup_vector, it returns the error value #N/A.

Examples:

A.2 to A.5 equals  1.1,2.1,3.1,4.1

B.2 to B.5 equals  40000,35000,15000,8000

C.2 to C.5 equals  Luxury, sport, utility, and basic transport.

In the view shown above:

MATCH(2.1,A.'2:A.'5) equals 2.

MATCH(2.5,A.'2:A.'5,-1) equals 3.

MATCH(5,A.'2:A.'5) equals #N/A

MATCH("luxury",C.'2:C.'5) equals 1

MATCH("LUXURy",C.'2:C.'5) equals 1

Related functions:

CHOOSE uses an index number to choose a value from the list of items designated by the arguments.

INDEX uses an index number to choose a value from a reference.

LOOKUP finds the specified value in a lookup table and returns the result that corresponds to that value.

Lookup Functions


MAX()
MAX(number1,[number2], . . .)

Purpose: The MAX function returns the largest value in the list of values specified by the arguments.

Arguments: number A number, logical value, text representation of a number, or a reference to a cell or range.

The MAX function can have from 1 to 40 arguments.   Arguments that are error values or text that cannot be translated into numbers cause errors.  If the argument is a range, empty cells, logical values, text, or error values are ignored.

Results: The largest number in the list of values or range specified by the arguments.

Examples:

MAX(10,9,8) equals 10

MAX("11",4,8) equals 11

If the range A.'1:C.2 contains the numbers 3, 15, 4, 9, 13, 17, then:

MAX(A.'1:C.2) equals 17.

Related functions:

 MIN returns the smallest value in the list of values specified by the arguments.

Statistical Functions


MID()
MID(text,start_number,number_of_characters)

Purpose: The MID function extracts the specified number of characters from a string of text starting at the specified location in the string.

Arguments: text A number, text, or a cell reference.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

start_number The number of the character in the string of text where you want to begin extracting text.  The first character in the string is numbered 1.

number_of_characters  The number of characters you want to extract from the string of text specified by text.  This argument must be greater than zero.

Results: A string of text that is the length specified by number_of_characters and is extracted from the location in text specified by start_number.

If Start_number is greater than the length of text, MID returns "" ( the empty text)

If Start_number is less than the length of text, MID returns "" ( the empty text)

If Start_number is less than the length of text, but start_number plus number_of_characters exceeds the length of text ,MID returns The characters up to the end of text.

If Start_number is less than 1 or  Number_of_characters is negative MID returns the error value #VALUE!

Examples:

MID("Edwin Moore",3,3) equals "win"

MID("Edwin Moore",7,10) equals "Moore"

MID("WXYZ",5,5) equals "", empty text

If the cell C.'10 contains "Average Daily Price", then:

MID(C.'10,9,5) equals "Daily"

Related functions:

LEFT extracts the specified number of leftmost characters from a string of text.

RIGHT extracts the specified number of rightmost characters from a string of text.

Text Functions


MIN()
MIN(number1,[number2], . . .)

Purpose: The MIN function returns the smallest value in the list of values specified by the arguments.

Arguments: number A number, logical value, text representation of a number, or a reference to a cell or range.

The MIN function can have from 1 to 40 arguments.   Arguments that are error values or text that cannot be translated into numbers cause errors.  If the argument is a range, empty cells, logical values, text, or error values are ignored.

Results: The smallest number in the list of values or range specified by the arguments.

Examples:

MIN(10,9,8) equals 8

MIN(11,"4",8) equals 4

If the range A.'1:C.2 contains the numbers 3, 15, 4, 9, 13, 17, then:

MIN(A.'1:C.2) equals 3.

Related functions:

MAX returns the largest value in the list of values specified by the arguments.

Statistical Functions


MINUTE()
MINUTE(serial_number)

Purpose: The MINUTE function returns the minute portion of a serial date/time number.

Arguments: serial_number A serial date/time value, a time function, or a reference to a cell that contains either a time function or a serial date/time value.

The argument can be given as text, such as "13:25:00" or "6:50:20 p.m.", instead of a number.  CA-Compete! automatically converts the text to the corresponding serial time number.  A text argument must be enclosed in double quotation marks.

Results: The value of the minute specified by the serial date/time value.  The minute is given as an integer, ranging from 0 to 59.

Examples:

MINUTE("4:05 AM") equals 5

MINUTE(32904.8) equals 12

MINUTE("3:00:40 PM) equals 0

If the cell B.'4 contains the function TIME(14,10,15), then

MINUTE(B.'4) equals 10.

Related Functions:

WEEKDAY converts a serial number to a day of the week.

DAY converts a serial number to a day of the month.

MONTH converts a serial number to a month of the year.

YEAR converts a serial number to a year.

HOUR converts a serial number to an hour of the day.

SECOND converts a serial number to a second.

NOW returns the serial number corresponding to the current date and time.

Date and Time Functions


MIRR()
MIRR(values,finance_rate,reinvest_rate)

Purpose: The MIRR function calculates the modified internal rate of return for an investment that generates an uneven stream of periodic payments.  MIRR is similar to IRR, but takes into account the cost of the money you will borrow to finance the investment and the interest received on cash generated by the investment that will be reinvested.

Arguments: values A reference to a vector that contains numbers.  These numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods.  There must be at least one positive value and one negative value in order to calculate the internal rate of return.

finance_rate A value that represents your estimate for what the rate will be.

reinvest_rate A value that represents the interest rate received on the reinvestment of cash.

MIRR uses the order of values to interpret the order of cash flows.  Be sure to enter your payment and income values in the correct sequence.

Also, the values argument must be a vector (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data.  Text, logical, and empty cell values in the vector specified by values are ignored.

Results: The interest rate per period of an investment or loan.  The rate of return calculated by MIRR is the interest rate corresponding to a "zero" net present value.

If values does not contain at least one positive value and one negative value, MIRR returns the error value #DIV!.

Use: The MIRR function is useful for calculating the internal rate of return on an investment when you want to take into consideration the cost of financing the investment and the reinvestment of cash generated.

The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

Examples: Imagine you own a bookstore and you have just completed your fifth year of operation.  When you started the business, you borrowed $200,000 at 11% annual interest to purchase stock and rent space.  Your net income during the first five years was $45,000, $42,000, $50,000, $53,000, and $60,000.  During these years you reinvested your profits, earning 12% annually.

To calculate the investment's modified rate of return after five years, use:

MIRR(Income.'1984:Income.'1989,11%,12%) equals 0.0929 equals 9.29%.

To calculate the modified rate of return after three years, use:

MIRR(Income.'1984:Income.'1987,11%,12%) equals -0.0845 equals -8.45%.

To calculate the five-year modified rate of return based on a reinvestment rate of 14%, use:

MIRR(Income.'1984:Income.'1989,11%,14%) equals 0.1010 equals 10.1%.

Related functions:

IRR gives the internal rate of return without considering financing costs or reinvestment gains.

NPV returns the net present value of an investment based on uneven periodic cash flows.

RATE returns the interest rate for an investment based on constant periodic cash flows.

Financial Functions


MOD()
MOD(number,divisor_number)

Purpose: The MOD function calculates the remainder from the division of the arguments.

Arguments: number A number, formula, or cell reference that is to be used as the dividend in the division operation.

divisor_number The number, formula, or cell reference by which you want to divide number.

Results: The remainder that results from dividing number by divisor_number.

If number is smaller than the divisor_number, then MOD returns number.  If number is exactly divisible by the divisor_number, then MOD returns 0.  If the divisor_number is 0, MOD returns the error value #DIV/0!.

Examples:

MOD(10,3) equals 1

MOD(4,8) equals 4

MOD(20,5) equals 0

MOD(-5,2) equals 1

MOD(5,-2) equals -1

Related functions:

INT rounds a number down to the nearest integer.

ROUND rounds a number to a specified number of digits.

TRUNC truncates a number to an integer.

Mathematical Functions


MONTH()
MONTH(serial_number)

Purpose: The MONTH function returns the month of the year that corresponds to a serial date number.

Arguments: serial_number A serial date value, a date function, or a reference to a cell that contains either a date function or a serial date value.

The argument can be given as text, such as "3/15/90" or "3-Mar-90", instead of a number.  CA-Compete! automatically converts the text to the corresponding serial date number.  A text argument must be enclosed in double quotation marks.

Results: The value of the month specified by the serial date value.  This will be a value between 1 and 12, with 1 representing January and 12 representing December.

If serial_number is a decimal number, MONTH ignores the numbers to the right of the decimal point which represent time.

Examples:

MONTH("5/23/90") equals 5

The serial date for March 31, 1990 is 32904; therefore:

MONTH(32904) equals 3.

If the cell B.'4 contains the function DATE(90,1,15), then

MONTH(B.'4) equals 1.

Related Functions:

WEEKDAY converts a serial number to a day of the week.

DAY converts a serial number to a day of the month.

YEAR converts a serial number to a year.

HOUR converts a serial number to an hour of the day.

MINUTE converts a serial number to a minute.

SECOND converts a serial number to a second.

NOW returns the serial number corresponding to the current date and time.

Date and Time Functions


N()
N(value)

Purpose: The N function translates the argument into a number.

Arguments: value A number, text, logical value, or cell reference.

Results: N translates values as follows:

If value is a number N returns that number.

If value is the logical value TRUE N return 1,

if value is an error value N returns that error value otherwise

N returns 0.

Use: It is not generally necessary to use the N function in a formula, since CA-Compete! automatically translates values as necessary.  This function is provided for compatibility with other worksheet programs.

Examples:

N(7) equals 7

N("7") equals 0, because "7" is text

If the cell A.'1 contains the logical value TRUE, then:

N(A.'1) equals 1.

If the cell B.'1 contains the text value "4/17/90", then:

N(B.'1) equals 0.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

T translates its argument into text.

Information Functions


NA( )

Purpose: The NA function returns the error value #N/A, which indicates that no value is available.

Arguments: Although the NA function has no arguments, you must include the empty parentheses with the function name.

Results: The error value #N/A.

Use: The NA function is provided for compatibility with other worksheet programs.  If you prefer, you can also type the value #N/A directly into a cell.

NA can serve as a placeholder function by marking empty cells.  By entering #N/A in cells where information is missing, you can avoid the problem of unintentionally including empty cells in your calculations.  All formulas that refer to a cell containing the error value #N/A will also return the error value #N/A.

Example:

NA() equals #N/A.

Information Functions


NOT()
NOT(logical)

Purpose: The NOT function reverses the value of the logical value specified by the argument.

Arguments: logical A logical value (TRUE or FALSE), a formula, a conditional test, or a reference to a cell that contains a logical value.

Use: The NOT function is used for more complex conditional testing in your models.  This function works in conjunction with the simple operators =,>,<,>=, <=, and <> and with the functions IF, AND, and OR.  Together, you can use these functions to develop compound conditions.

Results: The logical value TRUE if value is false, and FALSE if value is true.

If there are no logical values specified, the function returns the error value #VALUE!.

Examples:

NOT(TRUE()) equals FALSE

NOT(1+1=3) equals TRUE

If the range B.'1:B.'4 contains the values TRUE, TRUE, TRUE, and FALSE, then:

NOT( B.'1) equals FALSE.

NOT(B.'4) equals TRUE.

If the cell Units.'1989 contains the number 425 and the cell Units.'1990 contains the number 500, then:

NOT(Units.'1989>400) equals FALSE.

NOT(Units.'1989>Units.'1990) equals TRUE.

Related Functions:

AND returns the logical value TRUE if all arguments are true.

OR returns the logical value TRUE if any argument is true.

Logical Functions


NOW( )

Purpose: The NOW function returns the current date and time, according to your computer's built-in clock.

Arguments: Because the NOW function has no arguments, you must include the empty parentheses with the function name.

Results:  A serial value that includes both an integer (the date) and a fraction (the time).

The CA-Compete! date format m/d/y h:mm displays both the date and time portions of the NOW result.

Use: Values calculated with the NOW function change only when the model is calculated.   The NOW function does not update continuously.  When  the Automatic Calculation option is turned on, the model is calculated every time you enter a new value or formula.  When the Manual Calculation option is turned on, the model is calculated only when you chose the Options Calculate Now command.  To change the calculation setting, select Options Calculation; unless the calculation setting is changed, the Automatic Calculation option is turned on.

Examples: If your computer clock is correctly adjusted and it is 2:46:15 p.m. on February 23, 1990, then:

NOW() equals 32927.6155.

Six minutes later:

NOW() equals 32927.6196.

Related Functions:

WEEKDAY converts a serial number to a day of the week.

DAY converts a serial number to a day of the month.

MONTH converts a serial number to a month of the year.

YEAR converts a serial number to a year.

HOUR converts a serial number to an hour of the day.

MINUTE converts a serial number to a minute.

SECOND converts a serial number to a second.

Date and Time Functions


NPER()
NPER(rate,pmt,pv,[fv],[type])

Purpose: The NPER function calculates the number of periods that will be required to amortize a loan, given a specified periodic payment.

Arguments: rate The interest rate per period.

pmt The periodic investment, or the payment made each period.

pv The present value, or the lump-sum amount that the series of payments to be paid in the future is worth right now.

fv The future value, or a cash balance you want to be attained sometime in the future after the last payment is made.

type A value that indicates to CA-Compete! whether the payments are received at the beginning (type is 1) or at the end (type is 0) of each period.

The arguments rate, pmt, pv, and fv can be a number, text representation of a number, formula that returns a number, or a reference to a cell that contains any of these.

Results: The number of periods it will take to amortize a loan.

If the fv and type arguments are omitted, they default to 0.

For more information on the formula that NPER uses to calculate the number of periods it will take to amortize a loan, see the topic Annuity Functions

IUse: The NPER function is used to find the length of time necessary to accumulate a specific amount of money when the amount of the periodic payments and interest rate are known.

Examples:

NPER(13%/12,-75,-750,7500,1) equals 59

NPER(1%,-100,-1000,10000) equals 60

NPER(13%,-100,2000) equals 19

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

FV returns the future value of an investment.

IPMT returns the interest payment for an investment for a given period.

PPMT returns the principal payment for an investment for a given period.

PMT returns the periodic total payment for an investment.

PV returns the present value of an investment.

RATE returns the interest rate per period of an investment.

Financial Functions


NPV()
NPV(rate,value1,[value2], . . .)

Purpose: The NPV function calculates the net present value of an investment based on a series of periodic, although not necessarily constant, cash flows.  NPV also considers the cost of the investment and not just the inflows.

Arguments: rate The rate of discount over the length of one period.

value The inflows of cash, which must be equally spaced in time and occur at the end of each period.  NPV uses the order of value1, value2, . . . to interpret the order of cash flows.

NPV can have up to 40 arguments.  Arguments that are numbers, empty cells, logical values, or text representations of numbers are counted.  Arguments that are error values or text that cannot be translated into numbers are ignored.  If an argument is a reference, only numbers in the reference are counted; empty cells, logical values, text, or error values are ignored.

Results: A value that indicates the profitability of the specified investment.  In general, any investment that yields a net present value greater than 0 is considered profitable.

The NPV investment begins one period before the date of the value1 cash flow, and ends with the last cash flow in the list of arguments.  It is important to remember that the NPV calculation is based on future cash flows.  If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments.

Use: Like the PV function, NPV is useful for determining the profitability of an investment.  NPV, however, can be used to calculate the profitability of an investment where the stream of cash flows is uneven.

Examples: Suppose your company built a plant for $5 million and the following income is expected in the first five years of the plant's operation:  $1,150,000, $1,225,000, $1,300,000, $1,375,000, $1,450,000.  The annual discount rate is assumed to be 8%; this might represent the rate of inflation, or the interest rate of a competing investment.

To determine the net present value of the plant investment and cash flows displayed above, use:

NPV(8%,Income.'1990:Income.'1995) equals $133,841.2.

In this example, you include the initial $5 million cost as one of the values, because the payment does not occur at the beginning of the first period.

If a fire occurs at the plant during the sixth year and you assume a loss of $500,000, then the net present value of the plant after six years is determined by:

NPV(8%,Income.'1990:Income.'1995,-500000) equals -$157,904.

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

FV returns the future value of an investment.

IRR returns the internal rate of return for an investment.

PV returns the present value of an investment.

Financial Functions


OBJECTINDEX()
OBJECTINDEX(dimension_index,[reference])

Purpose: The OBJECTINDEX  function uses a dimension index number and a cell reference to return the index of the specified object.

Arguments:

 dimension_index The index number of the dimension that contains the object  whose index number you want to know.

reference A reference to a cell that is defined by the object whose index number you want to know.  For example, if you want to know the index number of the object Sales, reference could be a reference to any Sales cell in the model.  If reference is omitted, it defaults to reference the cell where the function is entered.

  The default dimensions are indexed in the following manner:  Dim1 is 1, Dim2 is 2, and Dim3 is 3.  If you rename these dimensions, they retain these index numbers.  All other dimensions in a model are indexed by order of creation.  For example, the fourth dimension added to the model will be indexed 4, the fifth dimension 5, and so on.

Results: The index number of the specified object.

Objects are numbered from left to right or top to bottom on a displayed dimension.  Index numbering for objects begins at 1.

If reference is not specified, it defaults to reference the cell containing the OBJECTINDEX function itself.  If you use an index number for dimension_index that is greater than the number of dimensions in the model, OBJECTINDEX returns the error value #VALUE!.

Examples: In a model , Dim1 has been renamed PRODUCTS, Dim2 FINANCIALS, and Dim3 TIME.  There are four objects on the YEARS dimension.  They are 1988, 1989, 1990, and 1991.

The object of PRODUCTS are Pdt 1, Pdt 2, Pdt 3 and Total Pdt.

The object of FINANCIALS are Units, Price, Sales, Total Costs, Net Operating Profits.

In the model descriped above:

OBJECTINDEX(1,Units.Pdt3.1989) equals 3.

OBJECTINDEX(2,Units.Pdt 3.1989) equals 1.

OBJECTINDEX(3,Units.Pdt3.1989) equals 2.

Related functions:

OBJECTNAME uses dimension and object index numbers to return the name of an object.

LABEL uses the dimension name and a cell reference to return the name of an object.

DIMENSION uses the dimension name to return the index number of a dimension.

DIMENSIONNAME uses a dimension index number to return the name of a dimension.

Lookup Functions


OBJECTNAME()
OBJECTNAME(dimension_index,object_index)

Purpose: The OBJECTNAME function uses dimension and object index numbers to return the name of the specified object as text.

Arguments: dimension_index  The index number of the dimension that contains the object  whose name you want returned.

object_index The index number of the object whose name you want returned.

Results: An object name as text.

Examples: In a model , Dim1 has been renamed PRODUCTS, Dim2 FINANCIALS, and Dim3 TIME.  There are four objects on the YEARS dimension.  They are 1988, 1989, 1990, and 1991.

The object of PRODUCTS are Pdt 1, Pdt 2, Pdt 3 and Total Pdt.

The object of FINANCIALS are Units, Price, Sales, Total Costs, Net Operating Profits.

In the model descriped above:

OBJECTNAME(1,1) equals "Pdt 1".

OBJECTNAME(2,4) equals "Total Costs".

OBJECTNAME(3,2) equals "1989".

Related functions:

OBJECTINDEX uses a dimension index number  and a cell reference to return the index number of an object.

LABEL uses the dimension name and a cell reference to return the name of an object.

DIMENSION uses the dimension name to return the index number of a dimension.

DIMENSIONNAME uses a dimension index number to return the name of a dimension.

Lookup Functions


OR()
OR(logical1,[logical2], . . .)

Purpose: The OR function determines if any of the values specified by the arguments is true.

Arguments: logical A logical value (TRUE or FALSE), a formula, a conditional test, or a reference to a cell or range that contains a logical value.

The OR function can have from 1 to 40 arguments.  Arguments that are empty cells, error values, or text that cannot be translated into logical values cause errors.  In a reference argument, however, all values except logical values are ignored.

Results: The logical value TRUE if any of the values specified by the arguments are TRUE.  If all of the values specified by the arguments are FALSE, OR returns the logical value FALSE.

If there are no logical values specified, the function returns the error value #VALUE!.

Use: The OR function is used for more complex conditional testing in your models.  This function works in conjunction with the simple operators =,>,<,>=, <=, and <> and with the functions IF, AND, and NOT.  Together, you can use these functions to develop compound conditions.

Examples:

OR(TRUE(),TRUE(),FALSE()) equals TRUE

OR(FALSE(),FALSE()) equals FALSE

OR(1+2=3,1+1=20) equals TRUE

If the range B.'1:B.'4 contains the values TRUE, FALSE, FALSE, and FALSE, then:

OR( B.'1:B.'4) equals TRUE.

If the cell Units.'1989 contains the number 425, then:

OR(Units.'1989>400,Units.'1989<410) equals TRUE.

OR(Units.'1989>500,Units.'1989<410) equals FALSE.

Related functions:

AND returns the logical value TRUE if all arguments are true.

NOT returns the logical value that is the reverse of its argument.

Logical Functions


PI()

Purpose: The PI function returns the number 3.14159265, the value of the constant  π.

Arguments: Although the PI function has no arguments, you must include the empty parentheses with the function name so that CA-Compete! recognizes it as a function.

Results: The number 3.14159265, which is the value of the constant π, accurate to 8 decimal places.

Examples:

PI() equals 3.14159265

PI()/3 equals1.04719755

COS(PI()/3) equals 1

Related functions:

Mathematical Functions


PMT()
PMT(rate,nper,pv,[fv],[type])

Purpose:

 The PMT function calculates the periodic payment required to amortize a loan given a specific number of periods and a constant interest rate.  PMT is an annuity function.

Arguments: rate The interest rate per period.

nper The total number of payment periods in the annuity or the term of the loan.

pv The present value, or the lump-sum amount that the series of payments to be paid in the future is worth right now (in this case, the principal amount of the loan).

fv The future value, or a cash balance, you want to be attained sometime in the future after the last payment is made.

type A value that indicates to CA-Compete! whether the payments are received at the beginning (type is 1) or at the end (type is 0) of each period.

The arguments rate, nper, pv, and fv can be a number, text representation of a number, formula that returns a number, or a reference to a cell that contains any of these.

Results: The amount of the constant periodic payments needed to amortize the loan specified by pv in the given period of time, nper.

If the fv and type arguments are omitted, they default to 0.

For more information on the formula that PMT uses to calculate the amount of the periodic payments, see the topic Annuity Functions

Use: The PMT function can be used to determine the amount of the periodic payment required to accumulate a specified amount of money when the interest rate and the number of payments are known.

Examples: The following PMT function finds the monthly payment due to repay a five-year $10,000 loan at 13% annual interest:

PMT(13%/12,60,10000) equals -$227.53.

If you plan to save $10,000 over the next five years by making monthly deposits to your savings account, then:

PMT(5.25%/12,60,0,10000,1) equals -$145.47.

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

FV returns the future value of an investment.

IPMT returns the interest payment for an investment for a given period.

NPER returns the number of periods (payments) for an investment.

PPMT returns the principal payment for an investment for a given period.

PV returns the present value of an investment.

RATE returns the interest rate per period of an investment.

Financial Functions


PPMT()
PPMT(rate,per,nper,pv,[fv],[type])

Purpose:

 The PPMT function calculates the payment on principal for the specified period during the amortization of a loan given periodic constant payments and a constant interest rate.

Arguments: rate The interest rate per period.

per A number that specifies the period.  Per must be a number in the range 1 to nper.

nper The total number of payment periods in the annuity.

pv The present value, or the lump-sum amount that the series of payments to be paid in the future is worth right now.

fv The future value, or a cash balance you want to be attained sometime in the future after the last payment is made.

type A value that indicates to CA-Compete! whether the payments are received at the beginning (type is 1) or at the end (type is 0) of each period.

The arguments rate, per, nper, pv, and fv can be a number, text representation of a number, formula that returns a number, or a reference to a cell that contains any of these.

Results: The amount of the periodic payment for the specified period that goes toward paying the principal on the loan.

If the fv and type arguments are omitted, they default to 0.

For more information on the formula that PPMT uses to calculate the principal portion of the specified payment, see the topic Annuity Functions.

Use: The PPMT function is used to determine the principal portion of the total periodic payment when the period, interest rate, and the total number of payments are known.

Examples: The following PPMT function finds the amount of principal repaid in the first month of a five-year $10,000 loan at 13% annual interest:

PPMT(.13/12,1,60,10000) equals -$119.20.

The principal repaid in the last yearly period of a twenty-year $150,000 loan at 11% interest is:

PPMT(11%,20,20,150000) equals -$16,969.70.

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

FV returns the future value of an investment.

NPER returns the number of periods (payments) for an investment.

IPMT returns the interest payment for an investment for a given period.

PMT returns the periodic total payment for an investment.

PV returns the present value of an investment.

RATE returns the interest rate per period of an investment.

Financial Functions


PRODUCT()
PRODUCT(number1,[number2] . . . )

Purpose: The PRODUCT function multiplies all numbers specified by the arguments and calculates the product.

Arguments: number A number, formula that returns a number, or reference to a cell that contains either of these.

The PRODUCT function can have from 1 to 40 arguments.  Arguments that are text representations of a number, logical values or empty cells are ignored, while arguments that are error values or text that cannot be translated into numbers cause errors.  In a reference argument, text, logical values, error values, and empty cells are ignored.

Results: A number that is the product of all numbers specified by the arguments.

Examples:

PRODUCT(3,2,1) equals 6

PRODUCT(3,SUM(5,5)) equals 30

Related functions:

SUM adds its arguments.

FACT returns the factorial of a number.

Statistical Functions


PROPER()
PROPER(text)

Purpose: The PROPER function capitalizes the first letter in each word in the specified string of text and converts all other letters to lower-case.

Arguments: text A number, text, or cell reference.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

Results: The same string of text as is specified by text but with the first letter of each word in the string capitalized and all other letters converted to lower-case.

Examples:

PROPER("b") equals "B"

PROPER("this is a TITLE") equals "This Is A Title"

PROPER("89sales") equals "89Sales"

If the cell A.'10 contains the text "TYPE 5", then:

PROPER(A.'10) equals "Type 5".

Related functions:

 LOWER converts any upper-case letters in a string of text to lower-case.

UPPER capitalizes every letter in the specified string of text.

Text Functions


PV()
PV(rate,nper,pmt,[fv],[type])

Purpose: The PV function calculates the present value of a series of periodic constant payments or a lump-sum payment.  PV is an annuity function.

Arguments:

rate The interest rate per period.

nper The total number of payment periods in the annuity.

pmt The periodic investment, or the payment made each period.

fv The future value, or a cash balance you want to be attained sometime in the future after the last payment is made.  You can use this argument in place of pmt when you want to calculate the present value of a lump-sum payment.

type A value that indicates to CA-Compete! whether the payments are received at the beginning (type is 1) or at the end (type is 0) of each period.

The arguments rate, nper, pmt, and fv can be a number, text representation of a number, formula that returns a number, or a reference to a cell that contains any of these.

Results: The present value of a series of payments or a lump-sum payment in the future, the opposite of future value ( see function specifications for FV).

If the fv and type arguments are omitted, they default to 0.

For more information on the formula that PV uses to calculate the present value of an investment, see the topic Annuity Functions.

Use: The PV function is useful for measuring the attractiveness of an investment.  The present value of an investment is the amount of money you are willing to expend today to receive another amount in the future.

Examples: Suppose you have an opportunity to buy a life insurance annuity for $70,000 that will pay $600 a month for the next 20 years.  Is it worth paying $70,000 today to earn $600 over the next 20 years assuming the money you pay out will earn 7% compounded annually?  To evaluate this investment, you must determine the present value of the stream of $600 payments you will receive.

If you use the PV function to determine the present value of the annuity, then:

PV(7%/12,12*20,,600,,0) equals -$77,3389.50.

The result is negative because it represents the money that you would have to pay today to receive a stream of payments over 20 years at $600 per month.  Since you would have to pay more than the $70,000 the life insurance company is asking to receive the same benefits, the annuity is an acceptable investment.

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

FV returns the future value of an investment.

NPER returns the number of periods (payments) for an investment.

IPMT returns the interest payment for an investment for a given period.

PPMT returns the principal payment for an investment for a given period.

PMT returns the periodic total payment for an investment.

RATE returns the interest rate per period of an investment.

Financial Functions


RAND()

Purpose: The RAND function generates a random number between 0 and 1.

Arguments: Although the RAND function has no arguments, you must include the empty parentheses with the function.

Results: A random number between 0 and 1.  This result changes each time you recalculate the model.  If you are using automatic recalculation, the value of the RAND function will change each time you make an entry in a model view or sheet

If you want to use RAND to generate a random number, but don't want the numbers to change every time the cell is calculated:

1. Type =RAND() in the formula bar.

2. Select Options Calculate Now.

3. Enter the formula.

Or, you can:

1. Enter the formula normally.

2. Copy the cell.

3. Choose Edit Paste Special.

4. Select Values.

CA-Compete! replaces the formula with the equivalent constant value.

Examples: RAND may be used to enter a series of random numbers in a range, or to pick a name or number at random from a range.

Related functions:

Mathematical Functions


RATE()
RATE(nper,pmt,pv,[fv],[type],[guess])

Purpose: The RATE function calculates the rate of return per period on an investment that generates a constant stream of periodic payments.

Arguments: nper The total number of payment periods in the annuity or term of the loan.

pmt The periodic investment, or the payment made each period.

pv The present value, or the lump-sum amount that the series of payments to be paid in the future is worth right now.

fv The future value, or a cash balance you want to be attained sometime in the future after the last payment is made.

type A value that indicates to CA-Compete! whether the payments are received at the beginning (type is 1) or at the end (type is 0) of each period.

guess A value that represents your estimate for what the rate will be.

The arguments nper, pmt, pv, fv, and guess can be a number, text representation of a number, formula that returns a number, or a reference to a cell that contains any of these.

Results: The interest rate per period of an investment or loan.

If the fv and type arguments are omitted, they default to 0.  If the guess argument is omitted, it defaults to 10%.

RATE is calculated by iteration, and can have zero or more solutions,  If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the error value #NUM!.

If RATE does not converge, try different values for guess.  Rate usually converges if given a guess between 0 and 1.

For more information on the formula that RATE uses to calculate the interest rate per period of an investment, see the topic Annuity Functions.

Use: The RATE function is useful for calculating the rate of return on an investment.

Examples:If an investment costs $5000 and will pay you five $1250 payments, then the annual rate of return on the investment is:

RATE(5,1250,5000,) equals .079308 equals 7.93% per year.

Related functions:

CAGR calculates the value that results when a specified value is increased at a periodic rate, for a specific number of periods.

 returns the future value of an investment.

NPER returns the number of periods (payments) for an investment.

IPMT returns the principal payment for an investment for a given period.

PPMT returns the principal payment for an investment for a given period.

PMT returns the periodic total payment for an investment.

PV returns the present value of an investment.

Financial Functions


REPLACE()
REPLACE(text,start_num,number_characters,new_text)

Purpose: The REPLACE function removes the specified number of existing characters at the specified location and substitutes new characters within a string of text.

Arguments: text The text where you want to make substitutions or a reference to a cell that contains the text you want.

start_number The number of the character where you want to begin replacing characters within text.  The first character in text is numbered 1.

num_characters The number of characters you want to replace beginning at the specified location within text.

new_text The new characters you want to substitute for existing text within text.

Results: A modified string of text.

Examples:

REPLACE("Market",5,2,"up") equals "Markup"

REPLACE("xyzabc",4,3,"???") equals "xyz???"

REPLACE("Sales and Price",1,LEN("Sales and Price"),"*") equals "*"

Related functions:

 SUBSTITUTE substitutes new text for the specified characters in a string of text.

TRIM removes spaces from a string of text.

Text Functions


REPT()
REPT(text,number_times)

Purpose: The REPT function repeats a character or a number of characters the specified number of times.

Arguments: text The text string you want repeated.

number_times A number that indicates the number of times you want the text string repeated.

Results: A string of repeated characters or groups of characters.  If number_times is 0, REPT returns "" (empty text).  If number_times is not an integer, it is truncated; for example, 2.34 becomes 2.

The result of the REPT function cannot be longer than 255 characters.

Examples:

REPT("a",3) equals "aaa"

REPT("*>",4) equals "*>*>*>*>"

REPT("Tim",2.3) equals "TimTim"

Related functions:

Text Functions


RIGHT()
RIGHT(text,[number_of_characters])

Purpose: The RIGHT function extracts the specified number of rightmost characters from a string of text.

Arguments: text A number, text, or cell reference.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

number_of_characters  The number of characters you want to extract from the beginning of the string of text specified by text.  This argument must be greater than zero.

Results: A string of text that is the length specified by number_of_characters and is extracted from the end of text.

If number_of_characters is greater than the length of text, then all of text is returned.  If number_of_characters is omitted, it defaults to 1.

Examples:

RIGHT("Edwin Moore",5) equals "Moore"

RIGHT("Edwin Moore") equals "e"

If the cell C.'10 contains "Average Price", then:

RIGHT(C.'10) equals "e" and RIGHT(C.'10,5) equals "Price"

Related functions:

 LEFT extracts the specified number of leftmost characters from a string of text.

MID extracts the specified number of characters from a string of text at a specific location in the string.

Text Functions


ROUND()
ROUND(number,num_of_digits)

Purpose: The ROUND function rounds the number specified by the argument to a specified number of digits.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

num_of_digits A number that indicates to what place you want to round number.

Results: A number rounded to the number of digits specified by the argument num_of_digits.

If num_of_digits is greater than 0, then Number is rounded to the specified number of decimal places.

If num_of_digit is 0, then Number is rounded to the left of the decimal place.

If num_of_digit is less than 0, then Number is rounded to the left of the decimal place.

Examples:

ROUND(2345.1367,2) equals 2345.14

ROUND(2345.1367,0) equals 2345

ROUND(2345.1367,-1) equals 2350

Related functions:

INT rounds a number down to the nearest integer.

TRUNC truncates a number to an integer.

MOD gives the remainder from division.

Mathematical Functions


SEARCH()
SEARCH(find_text,within_text,[start_at_number])

Purpose: The SEARCH function finds the specified text within another string of text.

Arguments: find_text The string of text you want to find within another string of text.  If find_text is "", CA-Compete! will match the first character that is searched–in other words, the character indicated by start_at_num.  This argument can contain the following wildcard characters:

Wildcard ? (Question mark) Meaning: Any single character can occupy the position.

Wildcard *(asterisk) Meaning: Any sequence of characters can occupy position.within_text The string of text in which you are searching for find_text.

start_at_number The number of the character in within_text where you want  to begin searching for find_text.  The first character of within_text is number 1.

All arguments that are text values must be enclosed in double quotation marks.

Results: A number that indicates the position of the first character in within_text that matches the text specified by find_text.  The SEARCH function is not case-sensitive.

If start_at_num is omitted, the default is 1, and CA-Compete! will begin the search with the first character of within_text.  If start_at_num is greater than the length of within_text, then SEARCH returns the error value #VALUE!.

Examples:

SEARCH("e","Average Price") equals 3

SEARCH("e","Average Price",4) equals 7

SEARCH("a","Average Price") equals 1

SEARCH("a","Average Price",5) equals 5

SEARCH("a*e","Average Price") equals 1

SEARCH("a*e","Average Price",2) equals 5

If the cell B.'10 contains the text "Average Price", then:

SEARCH("P*",B.'10) equals 9.

And, if the cell C.'1 contains the text "Price", then:

SEARCH(C.'1,B.'10) equals 9.

Related functions:

EXACT determines if two text values are identical.

LEN returns the length of a string of text

 like SEARCH, finds one text value within another, but is case-sensitive and does not accept wildcard characters

Text Functions


SECOND()
SECOND(serial_number)

Purpose: The SECOND function returns the second portion of a serial date/time number.

Arguments: serial_number A serial date/time value, a time function, or a reference to a cell that contains either a time function or a serial date/time value.

The argument can be given as text, such as "13:25:00" or "6:50:20 p.m.", instead of a number.  CA-Compete! automatically converts the text to the corresponding serial time number.  A text argument must be enclosed in double quotation marks.

Results: The value of the second specified by the serial date/time value.The second is given as an integer, ranging from 0 to 59.

Examples:

SECOND("4:05 AM") equals 0

SECOND(32904.8) equals 0

SECOND("3:00:40 PM) equals 40

If the cell B.'4 contains the function TIME(14,10,15), then

SECOND(B.'4) equals 15.

Related Functions:

WEEKDAY converts a serial number to a day of the week.

DAY converts a serial number to a day of the month.

MONTH converts a serial number to a month of the year.

YEAR converts a serial number to a year.

HOUR converts a serial number to an hour of the day.

MINUTE converts a serial number to a minute.

NOW returns the serial number corresponding serial number to the current date and time.

Date and Time Functions


SHARE()
SHARE(time1,share1,time2,share2,saturation,new_time)

Purpose: The SHARE function fits an S-curve in the range 0 to infinity to known data points and determines a new y-value for the new  x-value specified by the arguments.

Arguments: time1 A value that represents a specific point in time.

share1 Market share at time1; this value should be expressed as a percentage.

time2 A value that represents a specific point in time.

share2 Market share at time2; this value should be expressed as a percentage.

saturation The value at which the market becomes saturated–where the S-curve flattens out (approaches positive infinity); this value should be expressed as a percentage.

new_time The new time value which corresponds to the share value you want to determine.

Each of these arguments can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: A number that represents the market share which corresponds to new_time.

Use: The SHARE function is particularly useful for modeling behavior, such as market substitution, which follows an S-shaped curve.  This function can be used to predict the rate at which new technology, materials, products, or services will replace older technology, natural materials, or existing products or services.  It is useful for modeling economic behavior whenever substitution is an on-going process.  The SHARE function is easy to use because the data necessary to predict future share is not difficult to obtain.  For example, you can use two years of sales for each of the two product or service categories and an estimate of the ultimate saturation point (the point at which substitution stops) to predict the rate of substitution several years into the future.  For more information on substitution, see "A Simple Substitution Model of Technological Change," in John C. Fisher and Robert H. Pry, Technological Forecasting and Social Change.  or  Michael E. Porter's Competitive Advantage; Chapter 8, "Substitution," discusses the concepts that underlie CA-Compete!'s SHARE function.

Examples: The Acme Widgit company began producing a new aluminum widgit in 1983.  Many widgit users are substituting the new aluminum widgits for the older, heavier steel widgits.  Analysts at the Widgit Industry Association, however, predict that aluminum will never capture more than 70% of the widgit market because in 30% of the cases where widgits are used, they must be made of steel for reasons of strength and durability.  The view displayed below shows data that analysts at the Widgit Industry Association have collected since they began tracking the substitution of aluminum for steel widgits in 1985.

'1986.Aluminum Share equals 5%

'1988.Aluminum Share equals 25%

If managers at the Acme Widgit company want to determine what share of the total widgit market will be supplied by aluminum widgits in 1991, then:

SHARE(1986,'1986.Aluminum Share,1988,'1988.Aluminum Share,70%,1991) equals 64%.

If managers at the Acme Widgit company want to determine what share of the total widgit market will be supplied by aluminum widgits in 1993, then:

SHARE(1986,'1986.Aluminum Share,1988,'1988.Aluminum Share,70%,1993) equals 69%.

If managers at the Acme Widgit company predict that saturation of the widgit market with aluminum widgits will occur at 85% instead of  70%, and if they want to determine what the aluminum share of the total widgit market will be in 1993, then:

SHARE(1986,'1986.Aluminum Share,1988,'1988.Aluminum Share,85%,1993) equals 83%.

Related function:

COST calculates an exponential curve on a logarithmic scale and returns a y-value on the curve.

GROWTH calculates an exponential curve on a semilogarithmic scale and returns a y-value on the curve.

TREND calculates a regression line on a linear scale and returns a y-value on the line.

Statistical Functions


SIGN()
SIGN(number)

Purpose: SIGN returns the sign of the number specified by the argument as a value.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: If the argument is positive, SIGN returns 1.  If it is negative,  SIGN returns -1.  If the argument is 0, SIGN returns 0.

Examples:

SIGN(5) equals 1

SIGN(-5) equals -1

SIGN(0) equals 0

If the cell A.'3 contains the value -75, then:

SIGN(A.'3) equals -1.

If the cell A.'3 contains the positive value 75, then:

SIGN(A.'3) equals 1.

Related Functions:

ABS returns the absolute value of a number.

Mathematical Functions


SIN()
SIN(radians)

Purpose: The SIN function returns the sine of an angle.

Arguments: radians The number of radians in the angle whose sine you want to calculate.  If your argument is in degrees, multiply the argument by PI()/180 to convert the argument to radians.

Results: SIN returns the sine of radians, where radians is the number of radians in an angle.

[Examples:

SIN(1.5) equals 0.99749

SIN(-PI()) equals 0

SIN(PI()/2) equals 1

SIN(30*PI()/180) equals 0.5

Related Functions:

ASIN returns the arcsine of a number.

PI return the value π.

Trigonometric Functions


SIZE()
SIZE(keyword,[reference])

Purpose: The SIZE function determines the number of rows, columns, or objects in the specified range.

Arguments: keyword A text value that specifies the type of range information you want.

reference A reference to a cell or range of cells.  Reference can be a multidimensional range.

Results: SIZE returns the following information about reference:

If keyword is "row" SIZE returns The number of rows in the sheet range.If keyword is "col" SIZE returns The number of  columns in the sheet range. If keyword is a dimensionname ( e.g."Dim1" )SIZE returns the number of  object from the specified dimension in the range.

If reference is omitted, it defaults to the current selection.  If reference is a multiple selection, SIZE returns the error value #VALUE!.

Examples:

SIZE("row",A1:C4) equals 4

SIZE("col",A1:C4) equals 3

In a model,  which has three dimension, PRODUCTS, FINANCIALS, and YEARS, whereby Units, Price, Sales, Costs, Operating Profits are object of the FINANCIALS dimension and Pdt 1 - 4 are objects of the PRODUCTS dimension:

SIZE("PRODUCTS",Units.Pdt 1:Operating Profit.Pdt 4) equals 4.

SIZE("FINANCIALS",Units.Pdt 1:Operating Profit.Pdt 4) equals 5.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

TYPE returns a number indicating the data type of the contents of a cell.

The IS functions (ISBLANK, ISCONSISTENT, ISERR, etc.) all return information about the specified cell.

Information Functions


SLN()
SLN(cost,salvage,life)

Purpose: The SLN function determines the straight-line depreciation of an asset for a single period of time.

Arguments: cost The initial cost of the asset whose depreciation you are calculating.

salvage The value of the asset at the end of its life.

life The number of periods (typically years) over which the asset is being depreciated, also called the useful life.

Each of the arguments can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: The value of the depreciation of an asset for a single period.  The straight-line method of depreciation divides the depreciable cost (cost - salvage) evenly over the useful life of an asset.

SLN uses the following formula to calculate depreciation:

cost -salvage

--------------------

         life

Use: The SLN function is used to determine the straight-line depreciation of an asset when its residual value is known.

Examples: If you purchased a forklift for your warehouse for $10,000, and the forklift has a lifetime of 10 years and a salvage value of $1500, then:

SLN(10000,1500,10) equals $850 per year.

Related functions:

DDB function determines the depreciation of an asset for a specific period of time, using the double-declining balance method.

SYD returns the sum-of-years' digits depreciation of an asset for a specified period.

Financial Functions


SQRT()
SQRT(number)

Purpose: The SQRT function calculates the square root of the number specified by the argument.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: A number that is the square root of the argument. If the argument is negative, SQRT returns the error value #NUM!.

Examples:

SQRT(36) equals 6

SQRT(-36) equals #NUM!

SQRT(ABS(-36)) equals 6

If the cell B.'1 contains the number 100, then:

SQRT(B.'1) equals 10.

Related functions:

EXP returns the value of the constant e (which is equal to 2.7182818), raised to the specified power.

PRODUCT multiplies all of its arguments together.

Mathematical Functions


STDEV()
STDEV(number1,[number2], . . .)

Purpose: The STDEV function calculates the standard deviation of a population based on the sample of data specified by the arguments.  The standard deviation is calculated using the "nonbiased" or "n - 1" method.

Arguments: number A constant value or a reference to a cell or range that contains numbers.

The STDEV function can have from 1 to 40 arguments.  All text, logical, or empty cell values cause errors.

Results: The square root of the variance in a population based on the sample given by the arguments.  STDEV is the square root of VAR function: =SQRT (VAR ())

Use: The STDEV function, like VAR, is useful for measuring the dispersion of a group of numbers.  Standard deviation is the square root of the variance.

STDEV assumes the arguments are a sample of the population for which you are calculating the standard deviation.   If  the data you are using as arguments represents the entire population, you should use STDEVP to calculate the standard deviation.

Example:

STDEV(3000, 6000, 9000, 12000, 15000, 18000, 21000) equals 6480.741

Related functions:

STDEVP calculates the true standard deviation when you have all the data for a population.

VAR estimates the variance of a population based on a sample of data.

VARP calculates the true variance when you have all the data for a population.

Statistical Functions


STDEVP()
STDEVP(number1,[number2], . . .)

Purpose: The STDEVP function calculates the standard deviation when all the data for a population is specified by the arguments.  The standard deviation is calculated using the "biased" or "n" method.

Arguments: number A constant value or a reference to a cell or range that contains numbers.

The STDEVP function can have from 1 to 40 arguments.  All text, logical, or empty cell values cause errors.

Results: The square root of the variance in a population based on the data given by the arguments.  STDEV is the square root of VAR function: =SQRT (VAR ())

Use: The STDEVP function, like VARP, is useful for measuring the dispersion of a group of numbers.  Standard deviation is the square root of the variance.

STDEVP assumes the arguments are the entire population for which you are calculating the standard deviation.   If  the data you are using as arguments represents only a sample of the population, you should use STDEV to calculate the standard deviation.

Example:

STDEVP(3000, 6000, 9000, 12000, 15000, 18000, 21000) equals 6000

Related functions:

STDEV estimates the standard deviation of a population based on a sample of data.

VAR estimates the variance of a population based on a sample of data.

VARP calculates the true variance when you have all the data for a population.

Statistical Functions


SUBSTITUTE()
SUBSTITUTE(text,old_text,new_text,[instance_number])

Purpose: The SUBSTITUTE function substitutes new text for existing text in the specified text value.

Arguments: text The text where you want to make substitutions or a reference to a cell that contains the text you want.

old_text The existing text within text that you want to change.

new_text The new text that you want to substitute for old_text within text.

instance_number  The instance of occurrence of old_text where you want to substitute new_text within text.  If instance_num is specified, only that instance of old_text is replaced; otherwise old_text is replaced by new_text every time it occurs within text.

Results: A modified string of text.

The SUBSTITUTE function is case-sensitive.

Examples:

SUBSTITUTE("Market","et","up") equals "Markup"

SUBSTITUTE("Tote and Top","To","Ta",1) equals "Tate and Top"

SUBSTITUTE("Tote and Top","To",Ta",2) equals "Tote and Tap"

SUBSTITUTE("Tote and Top","To","Ta") equals "Tate and Tap"

Related functions:

REPLACE substitutes characters within a string of text at a specified starting point in the string of text.

TRIM removes spaces from a string of text.

Text Functions


SUM()
SUM(number1,[number2], . . .)

Purpose: The SUM function adds all the numbers specified by the arguments together.

Arguments: number A number, formula that returns a number, or reference to a cell that contains either of these.

The SUM function can have from 1 to 40 arguments.  Arguments that are text representations of a number, logical values, or empty cells are ignored, while arguments that are error values or text that cannot be translated into numbers cause errors.  In a reference argument, text, logical values, error values, and empty cells are ignored.

Results: The SUM function returns a single value that is the additive total of all values specified by the arguments.

Use: The SUM function is especially useful for adding the numbers in a range of cells together.  Wherever you would use a long formula to calculate the total for a number of figures, for example:

=(B.'1+B.'2+B.'3+ B.'4+B.'5+.B'6+B.'7+B.'8+B.'9+B.'10),

you can use a range argument and the SUM function instead:

 =SUM(B.'1:B.'10)

to achieve the same result.

Examples: The sales figures for the three products made by the Gadget company are displayed in the illustration below:

Sales.Pdt1 equals 1000

Sales.Pdt1 equals 750

Sales.Pdt1 equals 1250

Sales.Pdt1 equals 1200

To find total product sales:

SUM(Sales.Pdt 1:Sales.Pdt 4) equals 4200.

SUM(Sales.Pdt 1:Sales.Pdt 2, 1100) equals 2850.

Related functions:

AVERAGE averages its arguments.

COUNT counts the numbers in its list of arguments.

COUNTA counts the values in its list of arguments.

PRODUCT multiplies all of its arguments together.

Mathematical Functions


SYD()
SYD(cost,salvage,life,period)

Purpose: The SYD function determines the sum-of-years' digits depreciation for an asset for a specified period of time.

Arguments: cost The initial cost of the asset whose depreciation you are calculating.

salvage The value of the asset at the end of its life.

life The number of periods over which the asset is being depreciated, also called the useful life.

period The specific period for which you want to calculate the depreciation.

Each of these arguments can be a number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.  All four arguments must be positive numbers and period, and life must be given in the same units.  For example, if period is given in months, life must also be given in months.

Results:The value of the depreciation for the asset for the specified period.  The sum-of-the-years' digits method of depreciation accelerates the rate of depreciation, so that more depreciation expense occurs in earlier periods than in later ones. SLN uses the following formula to calculate depreciation:

(cost-salvage)*(life-period+1)

-------------------------------------------

                 life*(life+1)/2

Use: The SYD function is used to determine the sum-of-the-years' digits depreciation of an asset when the residual value of the asset and the period of depreciation are known.

Examples: If you purchased a forklift for your warehouse for $10,000 and the forklift has a lifetime of 10 years and a salvage value of $1500, then the yearly depreciation allowance for the first year is:

SYD(10000,1500,10,1) equals $1545.46.

The yearly depreciation allowance for the tenth year is:

SYD(10000,1500,10,10) equals $154.55.

Related functions:

DDB function determines the depreciation of an asset for a specific period of time, using the double-declining balance method.

SLN returns the straight-line depreciation of an asset for one period.

Financial Functions


T()
T(value)

Purpose: The T function translates the argument into text.

Arguments: value A number, text value, logical value, or reference to a cell that contains any of these.

Results: If value is text, T returns the specified text.  If value is anything else, T returns "" (the empty text).

Use: It is not generally necessary to use the T function in a formula, since CA-Compete! automatically translates values as necessary.  This function is provided for compatibility with other worksheet programs.

Examples:

 T(7) equals "", and the cell containing this function would be empty.

 T("7") equals "7"

If the cell A.'1 contains the logical value TRUE, then:

 T(A.'1) equals "", and the cell containing this function would be empty.

If the cell B.'1 contains the text value "4/17/90", then:

T(B.'1) equals "4/17/90".

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

The N function translates the argument into a number.

Information Functions


TAN()
TAN(radians)

Purpose: The TAN function returns the tangent of an angle.

Arguments: radians The number of radians in the angle whose tangent you want to calculate.  If your argument is in degrees, multiply the argument by PI()/180 to convert the argument to radians.

Results: TAN returns the tangent of radians, where radians is the number of radians in an angle.

Examples:

TAN(1.5) equals 14.1014

TAN(45*PI()/180) equals 1

Related Functions:

ATAN returns the arctangent of a number.

ATAN2 returns the arctangent of an angle defined by a point and the x-axis.

PI returns the value π.

Trigonometric Functions


TEXT()
TEXT(value,[format_text])

Purpose: The TEXT function formats a number in a specified format and converts the result into text.

Arguments: value A number, formula that returns a number, or reference to a cell that contains either of these.

format_text Any of the CA-Compete! formatting symbols ($, #, 0, etc.) except the asterisk (*) or question mark (?), arranged to indicate the format in which you want to display value.

Results: A number formatted in the specified format, which is text.

Use: The TEXT function is useful when you want to format a number and also convert it to text.  The formats available under the Format Number command change the format of a number, but do not convert it to text:  a number formatted with the Format Number command is still a number.

Examples:

TEXT(.356197,"0.00%") equals "35.62%"

TEXT(1/3,"0.00") equals "0.33"

TEXT("4/1/90", "mmmm d, yyyy") equals "April 1, 1990"

If the cell A.'3 contains the number 1408.546, then:

TEXT(A.'3,"$0.00") equals"$1408.55"

Related Functions:

DOLLAR rounds a number, formats it in currency format, and converts it to text.

FIXED rounds a number, formats it in decimal format, and converts it to text.

VALUE converts text to a number.

Text Functions


TIME()
TIME(hour,minute,second)

Purpose: The TIME function returns the serial number of the time specified by the arguments.

Arguments: hour A number that represents the hours portion of the time.  The number for hour should be an integer in the range 0 to 23.

minute A number that represents the minutes portion of the time.  The number for minute should be an integer in the range 0 to 59.

second A number that represent the seconds portion of the time.  The number for seconds should be an integer in  the range 0 to 59.

Results: A value that represents how much of the day has elapsed between midnight and the time indicated by the arguments.  Using the Format Number command, you can format the cell that contains the TIME function so that the resulting serial number is expressed in a familiar time format.

The serial time value is a decimal fraction ranging from 0 through 0.9999 and represents the times from 0:00:00 or 12:00:00 a.m. to 23:59:59 or 11:59:59 p.m.

Use: The TIME function is most useful in formulas where hour, minute, or seconds are formulas, not constants.

Since CA-Compete! will calculate the serial number from the date constant, It is not necessary to use the TIME function within a formula that uses dates as constants.  If you use the text form of the time, "14:30:00", or "5:40 p.m.", CA-Compete! automatically converts the text to a serial number.

Examples:

TIME uses the 24-hour time convention.  On the 24-hour clock, 4 a.m. is 4 o'clock and 4 p.m. is 16 o'clock.  Therefore to calculate the serial number for 3:45 PM, you would enter the following function in the cell:

TIME(15,45,0) equals .65625 equals 15:45:00.

TIME(12,0,0) equals .5 equals 12:00:00.

Related Functions:

TIMEVALUE returns the serial number of a time, but requires a text argument.

HOUR converts a serial number to an hour of the day.

MINUTE converts a serial number to a minute.

SECOND converts a serial number to a second.

NOW returns the serial number corresponding to the current date and time.

Date and Time Functions


TIMEVALUE()
TIMEVALUE(time_text)

Purpose: The TIMEVALUE function returns the serial number of the time specified by the text argument.

Arguments: time_text A time.  Times can be entered in any of the CA-Compete! built-in date formats, for example "1:40 PM" or "14:25:00".  Since it is text, the argument must be enclosed in double quotation marks.

Results: A value that represents how much of the day has elapsed between midnight and the time indicated by the argument.  Using the Format Number command, you can format the cell that contains the TIMEVALUE function so that the resulting serial number is expressed in a familiar time format.

Examples:

TIMEVALUE("3:50 AM") equals .159722

TIMEVALUE("16-Apr-90 3:50 AM") equals TIMEVALUE("3.50 AM") equals .159722

Related Functions:

TIME returns the serial number of a time, but requires a numeric argument.

HOUR converts a serial number to an hour of the day.

MINUTE converts a serial number to a minute.

SECOND converts a serial number to a second.

NOW returns the serial number corresponding to the current date and time.

Date and Time Functions


TREND()
TREND(known_y's,[known_x's],[new_x])

Purpose: The TREND function fits a regression line to the known x and y data points on a linear scale and returns a new y-value for the new x-value specified by the arguments.

Arguments: known_y's A reference to the vector that contains the known y-values on the straight line.

known_x's A reference to the vector that contains the corresponding known x-values on the straight line.

new_x A number or reference to a cell that contains the value of the x-value on the regression line which corresponds to the y-value you are seeking.

The arguments known_y's and known_x's must both be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_y's and known_x's must contain the same number of cells.

Results: The number that represents the y-coordinate on the regression line determined by the known_y's and the known_x's that corresponds to new_x.

The accuracy of the line calculated by TREND depends on the degree of scattering in the data specified by the arguments.  The more linear the data, the more accurate the TREND model.  TREND uses the method of least squares for determining the best fit for the data.  The regression line is calculated on a linear scale using the formula y=mx+b.

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vector specified by known_y's.  If the argument new_x is omitted, it defaults to 0.  If any of the numbers in known_y's is negative, TREND returns the error value #NUM!.

Use:

 The TREND function can be used to calculate trends and make near-term forecasts.  It is used by the Tools Forecast command when you choose "simple" as the type of growth you want to forecast.  The TREND function calculates growth as an incremental change rather than a percentage rate.  When you use the TREND function to calculate a line that passes through your data, CA-Compete! assumes that an incremental change in the driving variable (e.g., time, price of raw materials, units produced, headcount, etc.) will result in an incremental change in the driven variable (e.g., sales, production costs, administrative costs, etc.).

Examples: The model displayed below tracks information about the total number of shoppers and cars that visited a large suburban shopping mall during the first three Saturdays in May.

Saturday 1.Shoppers equals 8098

Saturday 2.Shoppers equals 9765

Saturday 3.Shoppers equals 8876

Saturday 1.Cars equals 2300

Saturday 2.Cars equals 2500

Saturday 3.Cars equals 2400

If you want to forecast the number of shoppers at the shopping mall on the fourth Saturday of May given the data recorded for the first three Saturdays in the month, then:

TREND(Saturday 1.Shoppers:Saturday 3.Shoppers,,4) equals 9691.

If the number of shoppers on the fourth Saturday in May is expected to be 8456 and you want to forecast the number of cars that will come use the mall parking lot, based on the data for the first three Saturdays in May, then:

TREND(Saturday 1.Shoppers:Saturday 3.Shoppers,Saturday 1.Cars:Saturday 2.Cars,8456) equals 2345.

Related function:

LINLINEST calculates a regression line on a linear scale and returns the slope or the y-axis intercept of the line.

GROWTH calculates an exponential curve on a semilogarithmic scale and returns a y-value on the line.

COST calculates an exponential curve on a logarithmic versus logarithmic scale and returns a y-value on the curve.

TREND2 calculates a regression plane on a linear scale and returns a new z-value on the plane.

SHARE calculates an S-curve and returns a new share-value on the curve.

Statistical Functions


TREND2()
TREND2(known_z's,known_y's,[known_x's],[new_y],[new_x])

Purpose: The TREND2 function uses the known x, y, and z data points to calculate a regression plane on a linear scale and returns a new z-value for the new pair of  x and y-values specified by the arguments.

Arguments: known_z's A reference to the vector that contains the known z-values on the plane

known_y's A reference to the vector that contains the corresponding known y-values on the plane.

known_x's A reference to the vector that contains the corresponding known x-values on the plane.

new_y A number or reference to a cell that contains the value of the x-coordinate on the plane which corresponds to the z-value you are seeking.

new_x A number or reference to a cell that contains the value of the y-coordinate on the plane which corresponds to the z-value you are seeking.

The arguments known_z's, known_y's, and known_x's must all be vectors (a range with a height, width, or depth of one cell).  A vector reference can refer to either a row, column, or cylinder (a one-dimensional range that extends into the depth of the model) of data; however, the rows, columns, or cylinders of data specified as known_z's, known_y's, and known_x's must contain the same number of cells.

Results: The number that represents the z-coordinate that corresponds to new_x and new_y  on the plane determined by the known_z's, known_y's, and known_x's.

The TREND2 function uses the formula y=m1*x1+m2*x2+b to plot a regression plane on a linear scale.

If the argument known_x's is omitted, it defaults to the vector that contains the values 1, 2, 3, . . . n, and is equal in size to the vectors specified by known_z's and known_y's.  If the arguments new_y and new_x are omitted, they default to 0.

Use: The TREND2 function is similar to the TREND function but it allows you to calculate trends and make near-term forecasts that depend upon two driving variables (known_y's and known_x's) instead of one.  This function calculates growth as an incremental change rather than a percentage rate.  It can be used to describe any relationship in which an absolute change in the two driving variables results in an absolute change in the driven variable .

The TREND2 function is used by the Tools Response Function command when you select the "Lin vs. Lin scale" option for the double cost curve you want to calculate.  You can use the TREND2 function to model economic behavior that demonstrates a linear relationship where two driving variables must be taken into consideration.  The TREND2 function is applicable in areas such as activity-based costing, manpower budgeting, sales forecasting, and feasibility or competitive analysis.

Examples: The model displayed below tracks information about the total number of guests, employees, and their cars at a luxury resort in Florida during the month of March.

Week 1.Cars equals 497

Week 2.Cars equals 561

Week 3.Cars equals 549

Week 1.Employees equals 225

Week 2.Employees equals 250

Week 3.Employees equals 250

Week 1Guests equals 544

Week 2Guests equals 622

Week 3.Guests equals 598

If you want to forecast the number of cars that will be parked at the resort during the fourth week of March, when the number of guest is expected to be 640 and the number of employees 260, then :

TREND2(Week 1.Cars:Week 3.Cars,Week 1.Guests:Week 3.Guests,Week 1.Employees:Weeks 3.Employees,640,260) equals 580.

Related function:

COST2 calculates a regression plane on a logarithmic scale and returns a new z-value on the plane.

GROWTH2 calculates a regression plane on a semilogarithmic scale and returns a z-value on the plane.

TREND calculates a regression line on a linear scale and returns a y-value on the line.

Statistical Functions


TRIM()
TRIM(text)

Purpose: The TRIM function removes all spaces from the specified text, except for one space between words.

Arguments: text A number, text, or cell reference.

Results: A string of text without any spaces, except for one space between words.

Examples:

TRIM("removes    extra  spaces    from    text") equals "removes extra spaces from text"

TRIM("We    the   people") equals "We the people"

Related functions:

REPLACE substitutes characters within a string of text at a specified starting point in the string of text.

SUBSTITUTE substitutes new text for the specified characters in a string of text.

Text Functions


TRUE()

Purpose: The TRUE function returns the logical value TRUE

Arguments: The TRUE function has no arguments.  You do not have to include the empty parentheses with the function name.

Results: TRUE returns the logical value TRUE.

Example: TRUE() equals TRUE

Related functions:

FALSE returns the logical value FALSE.

Logical Functions


TRUNC()
TRUNC(number)

Purpose: The TRUNC function truncates the number specified by the argument to an integer.

Arguments: number A number, text representation of a number, formula that returns a number, or reference to a cell that contains any of these.

Results: A number with no decimal places.

Examples:

TRUNC(45.136) equals 45

TRUNC(3.67) equals 3

TRUNC(-3.67) equals -3

Related functions:

INT rounds a number down to the nearest integer.

ROUND rounds a number to a specified number of digits.

MOD gives the remainder from division.

Mathematical Functions


TYPE()
TYPE(value)

Purpose: The TYPE function returns a number that indicates the data type of the argument.

Arguments: value A number, text, logical value, error value, or reference to a cell that contains any of these.

Results: TYPE returns 1 if value is a number or refers to a number, 2 if it is or refers to a text, 4 for a logical value and 16 in case of an error value.

Examples:

TYPE(13) equals 1

TYPE("Sam and Joann") equals 2

If the cell A.'1 contains the value TRUE, then:

TYPE(A.'1) equals 4.

If the cell B.'3 contains the value, #NUM!, then:

TYPE(B.'3) equals 16.

Related functions:

CELL returns information about the formatting, location, or contents of a cell.

SIZE returns the number of rows, columns, or objects in a range.

The IS functions (ISBLANK, ISCONSISTENT, ISERR, etc.) all return information about the specified cell.

Information Functions


UPPER()
UPPER(text)

Purpose: The UPPER function capitalizes all letters in the specified string of text.  Characters in the string of text that are not letters remain unchanged.

Arguments: text A number, text, or cell reference.  If the argument is a string of text, the argument must be enclosed in double quotation marks.

Results: The same string of text as specified by text, but all letters in the string are capitalized.

Examples:

UPPER("b") equals "B"

UPPER("Price") equals "PRICE"

If the cell A.'10 contains the text "type 5", then:

UPPER(A.'10) equals "TYPE 5".

Related functions:

PROPER capitalizes the first letter in each word in the specified string of text.

UPPER capitalizes every letter in the specified string of text.

Text Functions


VALUE()
VALUE(text)

Purpose: The VALUE function converts a number entered in a model in text format to a number.

Arguments: text A text value or reference to a cell that contains text.  If text is a text value, the argument must be enclosed in double quotation marks.

Results: A numeric value.

Use: It is not necessary to use the VALUE function in a formula, since CA-Compete! automatically converts the text to numbers, as necessary.  This function is provided for compatibility with other worksheet programs.

Examples:

VALUE("$2500") equals $2500

VALUE("2/15/90") equals the serial number 32919

If the cell A.'3 contains the text entry ="845", then:

VALUE(A.'3) equals 845.

Related Functions:

DOLLAR rounds a number, formats it in currency format, and converts it to text.

FIXED rounds a number, formats it in decimal format, and converts it to text.

TEXT converts a number to text.

Text Functions


VAR()
VAR(number1,[number2], . . .)

Purpose: The VAR function calculates the variance of a population based on the sample specified by the arguments.

Arguments: number A constant value or a reference to a cell or range that contains numbers.

The VAR function can have from 1 to 40 arguments.  All text, logical, or empty cell values cause errors.

Results: The variance in a population based on the sample given by the arguments.  VAR uses the following formula to calculate the variance:

nΣ(x²)-(Σx)²

----------------

   n(n-1)

Use: The VAR function is useful for measuring the dispersion of a group of numbers.

VAR assumes the arguments are a sample of the population for which you are calculating the variance.   If  the data you are using as arguments represents the entire population, you should use VARP to calculate the variance.

Examples:

VAR(3, 6, 9, 12, 15, 18, 21)  equals 52.28571

Related functions:

STDEV estimates the standard deviation of a population based on a sample of data.

STDEVP calculates the true standard deviation when you have all the data for a population.

VARP calculates the true variance when you have all the data for a population.

Statistical Functions


VARP()
VARP(number1,[number2], . . .)

Purpose: The VARP function calculates the variance when all the data for a population is specified by the arguments.

Arguments: number A constant value or a reference to a cell or range that contains numbers. The VARP function can have from 1 to 40 arguments.  All text, logical, or empty cell values cause errors.

Results: The variance in a population based on the data given by the arguments.  VARP uses the following formula to calculate the variance:

nΣ(x²)-(Σx)²

----------------

        n²

Use: The VARP function is useful for measuring the dispersion of a group of numbers.

VARP assumes the arguments are the entire population for which you are calculating the variance.   If  the data you are using as arguments represents a sample of the population, you should use VAR to calculate the variance.

Examples: The view below shows the number of bags of fertilizer that were sold each month by the Shadybrook Nursery in 1989.

VARP(3, 6, 9, 12, 15, 18, 21) equals 44.81633

Related functions:

STDEV estimates the standard deviation of a population based on a sample of data.

STDEVP calculates the true standard deviation when you have all the data for a population.

VAR estimates the variance of a population based on a sample of data.

Statistical Functions


VECTOR()
VECTOR(value1,[value2], . . .)

Purpose: The VECTOR function creates a vector (one-dimensional range) that contains the series of values specified by the arguments.

Arguments: value A number, formula that returns a number, or reference to a cell that contains either of these.

The VECTOR function can have from 1 to 40 arguments.  All text, logical, or empty cell values cause errors.

Results: A vector (a one-dimensional range that has a height, width, or depth of one cell) that contains the values specified by the arguments.

Use: The VECTOR function can only be used as an argument of a function that requests a range or vector.  You can use VECTOR to create a vector that contains values that are dispersed throughout a model or do not exist in the model.

Examples:

A.'1  equal  10

B.'2  equal  20

C.'1  equal  30

C.'2  equal 40

C.'4  equal 70

D.'1 through D.'4 equal 100, 120, 130, 160

TREND(D.'1:D.'4,VECTOR(10,20,30,40),C.'4) equals 213

TREND(D.'1:D.'4,VECTOR(A.'1,B.'2,C.'1,C.'2),C.'4) equals 213

Related functions:

Statistical Functions


WEEKDAY()
WEEKDAY(serial_number)

Purpose: The WEEKDAY function is used to determine the day of the week that corresponds to a serial date number.

Arguments: serial_number A serial date value, a date function, or a reference to a cell that contains a date function or a serial date value.

The argument can be given as text, such as "3/15/90" or "3-Mar-90", instead of a number.  CA-Compete! automatically converts the text to the corresponding serial date number.  A text argument must be enclosed in double quotation marks.

Results: A number from 1 to 7 that matches the day of the week specified by the serial date value.  Sunday is represented by number 1 and Saturday by number 7.

If serial_number is a decimal number, WEEKDAY ignores the numbers to the right of the decimal point, which represent time.

Use: The WEEKDAY function can be very helpful when used in conjunction with a lookup table in which the numbers for the day of the week (1,2, 3, . . . 7) are listed in one column and the corresponding day of the week as text (Monday, Tuesday, etc.) in the next.  You can also use WEEKDAY to retrieve the text name of a day of the week for a specific date.

Examples:

WEEKDAY("1/1/90") equals 2

The serial number for July 4, 1989 is 32693, so

WEEKDAY(32693) equals 3.

If the cell A.'1 contains the function DATE(90,2,14), then:

WEEKDAY(A.'1) equals 4.

Related Functions:

DAY converts a serial number to a day of the month.

MONTH converts a serial numberto a month of the year.

YEAR converts a serial numberto a year.

HOUR converts a serial numberto an hour of the day.

MINUTEconverts a serial numberto a minute.

SECONDconverts a serial numberto a second.

NOWreturns the serial numbercorresponding to the current date and time.

Date and Time Functions


YEAR()
YEAR(serial_number.)

Purpose: The YEAR function returns the year that corresponds to a serial date number.

Arguments: serial_number A serial date value, a date function, or a reference to a cell that contains either a date function or a serial date value.

The argument can be given as text, such as "3/15/90" or "3-Mar-90", instead of a number.  CA-Compete! automatically converts the text to the corresponding serial date number.  A text argument must be enclosed in double quotation marks.

Results: The value of the year specified by the serial_number

If serial_number is a decimal number, YEAR ignores the numbers to the right of the decimal point, which represent time.

Examples:

YEAR("6/13/87") equals1987

YEAR(.007) equals 1900

The serial date for March 31, 1990, is 32904, therefore:

YEAR(32904) equals 1990.

If the cell B.'4 contains the function DATE(91,1,15), then

YEAR(B.'4) equals 1991.

Related Functions:

WEEKDAYconverts a serial numberto a day of the week.

DAYconverts a serial numberto a day of the month.

MONTHconverts a serial numberto a month of the year.

HOURconverts a serial numberto an hour of the day.

MINUTEconverts a serial number to a minute.

SECONDconverts a serial numberto a second.

NOWreturns the serial numbercorresponding to the current date and time.

Date and Time Functions


Glossary

Active Cell

ASCII

Argument

Cell

Clipboard

CA-Compete Database

Dimension

Displayed Dimension

External Links

File Format

Fixed Dimension

Fixed Object

Fixed Object Box

Formula

Formula Scope

Formulabar

Function

Global Cell

Global Dimension

Global Formula

Global mode

Global mode icon

Global mode indicator

Logical Value

Multiple Selection

Object

Object Selector

Pivoting

Range

Reference

Reference Type

Scrollbar

Serial Number

Sheet

Statusbar

Serial Number

Term

Terms Box

Terms Formula

Value

Vector

View

Worksheet Cell

Worksheet Dimension

Worksheet Mode

Workspace


Serial Number

A serial number is the date-time code used by CA-Compete! for date and time calculations.  Numbers range from 0 to 65380, corresponding to the dates January 1, 1900, through December 31, 2078.  Numbers to the right of the decimal point in a serial date number represent the time; numbers to the left resent the date.  For example, the date-time combination 12:00 p.m., January 1, 1901, is represented as 367.5.


ASCII

American Standard Code Instruction Index. A standard code fot representing characters on most computers.


Argument

Information you supply to a CA-Compete function for calculation.


Clipboard

The holding place for information being copied or moved.


File Format

The way information in a document is stored in a file. Different programs use different file formats.


Formula

A sequence of values, cell references, functions or operators that is contained in a cell and produces a new value from existing values.


Formulabar

A bar on top of your window to enter and edit formulas and values for the selected cell. Depending on the selection it is also used for naming objects and dimensions and for editing term formulas.


Function

A built-in formula that take a series of arguments, uses them to perform an operation and returns a result. Functions are entered in the formulabar as part of a formula.


Logical Value

There are two logical values : TRUE and FALSE. Logical Values are used as arguments for logical functions, like the first argument of the IF function. The result of a formula, which contains a equation, like 44 > 39 is a logical value.


Multiple Selection

A selection of cells that includes more than one rectangular range of cells.


Range

A rectangular section of more than one cell in a view or a sheet.


Reference

An address of a cell in a sheet: SheetName.rowletter.columnnumber.

An address of a cell in the CA-Compete database: objectnames to identify a cell seperated by periodes


Reference Type

By default references are relative (e.g. Sales.P1). To mark references as absolute the '$' sign is used (e.g. $Sales.$P1). You can also mix those types. (e.g. $Sales.P1 is absolute in one and relative in the other dimension)


Scrollbar

Bars along the right and bottom side of a window that allow you to scroll verically or horizontally using a mouse.


Statusbar

Displays various information about the current status of the program at the bottom of the screen.


Dimension

A dimension groups a number of objects that are related, because they have common characteristics. Products, divisions or cost centers in a company, regions of the world, and competitors are all good examples of groups of objects which might each belong on a separate dimension.

The CA-Compete database supports up to 12 dimensions.


Worksheet Dimension

A worksheet dimension is not globalized.  That means, you can work with any object on the dimension, and all subsequent operations apply only to selected objects.


Global Dimension

A global dimension has been globalized–that is, the whole dimension is selected, and all subsequent operations apply to every object on the dimension.  You can also change the name of a dimension when it is globalized.


Object

Each item on a dimension is an object, and each object is part of a model's basic structure.  Objects are used to identify and locate information stored in the model database.  The intersection of one object  from every dimension defines each cell in a model; CA-Compete! uses the names of these intersecting objects to reference each cell.


Object Selector

The object selector is a dialog box from which you can select the objects you want to reference in the formula bar or a text box in a dialog. The object selector is useful when you want to include one or more objects in a cell reference or several cell references in the formula you are writing without typing.


CA-Compete Database

Each CA-Compete! model has a database, which consists of data and the model structure.  The model structure is simply a set of dimensions with labels (objects) on each dimension.  Data is stored in this structure, and an object from each of the dimensions is used to identify and locate data in the model.


View

Views are windows onto the database.  Each view is a two-dimensional slice through the database, which you can use to view data stored in the model, modify the structure of a model, and enter or manipulate data in the database.  The basic parts of a view are the dimensions, objects, and cells in the displayed workspace.  In each view, the horizontal and vertical axes represent dimensions in the model, and the row and column headings are objects in the database.  The intersection of one object from each dimension in the model is a cell, the basic unit of a model.  Views have the functionality of a spreadsheet and provide access to all of CA-Compete!'s functions and capabilities.


Sheet

CA-Compete! sheets are spreadsheets that provide a scratchpad for your calculations.  Although sheets are integral part of a CA-Compete! model, they are not part of the model database.  A sheet can contain inputs to and outputs from the model database; unlike views, however, sheets cannot be used to modify the structure of the model.  Each sheet is a rectangular grid of 256 columns and 4096 rows; the intersection of each row and column is a spreadsheet cell.  Since row and column headings in a sheet do not represent objects in the model database, they cannot be renamed.  However, you can copy and paste data and establish dynamic  links between sheets and views and vice versa.  Sheets have the functionality of a spreadsheet and provide access to CA-Compete!'s powerful tools such as Allocation, Forecast, Response Functions, Goal Seek, Data Tables, and Audit Trail. You can also use the formatting capabilities available in sheets to create presentation quality reports.


Active Cell

The active cell is the cell surrounded by a bold border.  When a cell is active, its contents appear in the formula bar.  Before you can enter or manipulate data in a cell, the cell must first be active.  You can change the active cell by clicking another cell with the mouse or using the direction keys to change the position of the cursor.


Cell

A view cell is the basic unit of a model.  Each of these cells is the intersection of one object from each of the dimensions in a model.  You can enter values or formulas in a cell. Data entered into a view is stored in the model database. Sheets also have cells.  These cells are the intersection of one row and one column.


Fixed Dimension

In models that have more than two dimensions, additional dimensions are represented by boxes located between the formula bar and the column headings.  Each fixed dimension box represents another dimension in the model.  A new model has three dimensions; think of a three-dimensional model as a cube where the three dimensions are height, width, and depth.  The object that appears in the fixed dimension box indicates which slice of data from the cube is displayed in the view.


Fixed Object

A fixed object is an object displayed in a fixed dimension box.


Fixed Object Box

The fixed object is an area in a view on top of the column headers surrounded by the global mode icons and the Up/down-arrows, where the fixed object (dimension) is displayed. Views of a N-dimensional model have (N-2) fixed object boxes.


Displayed Dimension

The row and column headings in a view display the objects on two dimensions of the model.  These are the displayed dimensions; one is displayed vertically and the other horizontally.


Vector

A one dimensional range.


Value

A value can be text, a number (including a date or time), a logical value (the value TRUE or the value FALSE), or an error value (a value which is preceded by the number sign (#) and signifies that CA-Compete! cannot properly calculate the formula for the cell).


Global Cell

Global cells are the cells that appear in a view when one or more dimensions have been globalized.  Each global cell represents a multidimensional range which includes all cells defined by every object on each of the global dimensions that intersects with the selected object on the worksheet dimensions.


Worksheet Cell

Worksheet cells are cells that appear in a view when none of the dimensions is globalized.


Global Mode

Whenever one or more dimensions in a model have been globalized, the model is in global mode, and the red "GLOBAL" mode message is displayed in the status bar.  When you are in worksheet mode, the "GLOBAL" mode message disappears.


Worksheet Mode

Whenever all dimensions in a model are worksheet dimensions, the model is in worksheet mode.


Global Formula

Global formulas are entered and stored in global cells.  The multidimensional range that a global cell represents defines the scope of the global formula entered in that cell.


Global mode icon

The cell or globe icons that appear to the left of each fixed dimension box and to the left of the Terms box are available only when the Global icons option in the Options Workspace dialog box is turned on.  Each of these icons represents a dimension in the model.   When you click on a cell icon (a small green square), you globalize the dimension it represents, and the worksheet dimension becomes a global dimension.  When a dimension is globalized, the cell icon changes to a globe icon (a small red circle).  Clicking on a globe icon changes the global dimension back to a worksheet dimension and makes the cell icon reappear.  Whenever a globe icon is visible, the model is in global mode and the global mode indicator is visible in the status bar.


Global mode indicator

The global mode indicator is the red "GLOBAL" message that appears to the right in the status bar whenever the view is in global mode.


Term

A term is an alternative unit of measure in which data entered in a model may be viewed.  For example, if you enter cost data in a model in dollar values. Terms encompass a wide variety of user-defined formulas.


Terms Box

The terms box is located directly below the first fixed dimension box.  In the terms box, you can define and select different measures for viewing your data.


Terms Formula

A formula associated with a term. Used to view values in a term and to enter values and formulas in a term.


Formula Scope

A formula's scope is the multidimensional range that is selected when you enter the value or formula in a cell.  This is the range of cells where the formula you create is available in the model.  You can use the Formula Formula Scope command or the global indicators to select a multi-dimensional range and determine formula scope.


External Links

An external link is an interactive link between two Windows Applications. CA-Compete! can act as a server (provide data to other applications) and as a client (receive data from other applications).


Pivoting

Reversing the positions of any two dimensions is called pivoting.  You can exchange a fixed dimension with a displayed dimension, or you can change how the displayed and fixed dimensions are arranged.


Workspace

The arrangement of open model windows in your CA-Compete! application window at any time is called a workspace.   The workspace is nothing more than the current display in the application window.


Keyboard
Using the Keyboard

F1	Start Win-Help.

F2	Edit Formulabar.

F3	Brings up the object selector to paste selection.

F4	Toggle absolute/relative references.

F6	Toggle Terms.

F7	Apply Global Formulas to Selection.

F8	Toggle Selection between a WorkSheet Cell the Global Cell.

F9	Open Info Window.

Ctrl-F4	Close the active window.

Ctrl-Shift-F6	Make previous window active.

Ctrl-F6	Make next window active.

Ctrl-F5	Restore window.

Ctrl-F7	Move the window.

Ctrl-F8	Resize the window.

Ctrl-F10	Maximize the window.

Ctrl-A	Arrange all windows.

Crtl-C	Calculate now.

Ctrl-D	Fill Down

Ctrl-G	Select a cell to become the active one.

Ctrl-L	Fill Left

Ctrl-N	Toggle between annotaion and formula mode.

Ctrl-R	Fill Right

Ctrl-U	Fill Up

Ctrl-Shift-F	Full Recalc mode

Ctrl-Shift-P	Starts the diagnostic routine.

Shift-Del	Cut selection

Del	Clear selection

Ctrl-Ins	Copy selection to clipboard.

Shift-Ins	Paste clipboard contents to selection.


Procedures

Data Manipulation:

Entering data in a cell

Entering data in a range

Creating a range

Change absulute and relative references

Referencing cells in a View

Referencing cells in a Sheet

Referencing a range

Copy and Pasting Data

Deleting Data

External Links:

Creating Links between Applications

Importing Data Into CA-Compete!

Exporting Data from CA-Compete!

Turning CA-Compete! DDE Links off and on

Disable DDE Requests

Global Formulas:

Defining Formula Scope

Expanding Formula Scope

Shrinking Formula Scope

Applying Global Formulas

Applying Global Formulas to cells

Applying Global Formulas to a Range

Applying Global Formulas to its total Scope

Update a Global Formula

Overwrite a Global Formula

Clear a Global Formula

Global Mode:

Switch To Global Mode

Exiting Global Mode

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode

Deleting a Global Formula from a Model

Model Loading and Saving:

Starting a Model

Open an Existing Model

Save and Name a Model

Close a Model

Saving the Workspace

Navigation:

Changing the displayed object

Changing a Views Orientation

Changing a Sheets Orientation

Structure Editing:

Naming Dimensions

Naming Objects

Naming Displayed Objects

Naming Fixed Objects

Displaying all Objects on a Fixed Dimension

Delete an Object

Adding an Object

Adding a Dimension

Positioning an object on a Dimension

Term Handling:

Create a term

Defing how a term is applied

Assigning a format to a term

Defining term Formulas

View Data in a Term

Enter Data in a Term

Views and Sheets:

Naming a View or a Sheet

Working with multiple windows

Close a View or a Sheet

Changing the Column Width

Hiding and Showing Objects


Starting a Model

Whenever you want to create a structure tailored to a specific data analysis, you will use a new CA-Compete! file to develop your model.

To open a new CA-Compete! file:

•Double-click on the CA-Compete! icon in the Main window of the Windows Program Manager if you do not have CA-Compete! running already.

Or, if you have another CA-Compete! file open:

•Select File New in the CA-Compete! window.

A new CA-Compete! model will appear on your computer screen.  You are now ready to create your own CA-Compete! structure.

Related topics:

CA-Compete! Procedures

Open an Existing Model

Save and Name a Model

Close a Model

Saving the Workspace


Naming Dimensions

All new CA-Compete! models have three default dimensions.  Since the default dimensions contain a limited number of objects, you need to change the number and names of the objects and dimensions in a CA-Compete! model to create the structure you want.

To rename a dimension:

1.Select an object on the dimension you want to rename.

2.Choose Edit Rename Dimension.

  The Rename Dimension dialog box appears.

  The current dimension name is displayed in the Dimension box.

3.Type the new dimension name in the Dimension box.

4.Select OK or press enter.

The new dimension name will appear in the reference area of the formula bar whenever one of the objects on the dimension is selected.

To rename a dimension in global mode:

One way to rename a dimension by globalizing the dimension, selecting the dimension name is to globalize it, select the dimension name that is displayed, and then type a new name in the formula bar:

1.Globalize the dimension you want to rename.

2.Select the row heading, column heading, or fixed dimension box where the current dimension name appears.

3.Type the new dimension name in the formula bar (press backspace to erase characters if you want to make corrections).

Press enter or select the enter box with the mouse.

Note:	If you make a mistake, you can cancel any changes you have made to a row heading, column heading, or fixed dimension box by pressing escape or clicking the cancel box with the mouse before pressing enter.

Related topics:

CA-Compete! Procedures

Naming Objects

Naming Displayed Objects

Naming Fixed Objects

Adding a Dimension


Naming Objects

The view that appears when you open a new CA-Compete! model looks like a spreadsheet, however, the row and column headings in a CA-Compete! view are objects in the model database, not just alphanumeric coordinates.  You can rename these objects, delete those you do not need, or add more of them to a dimension if there are not enough.  Renaming these objects enables you to identify data stored in your model in a meaningful way.

You have to distinguish between Naming Fixed Objects and

Naming Displayed Objects.

Related topics:

CA-Compete! Procedures

Naming Dimensions

Naming Displayed Objects

Naming Fixed Objects

Delete an Object

Adding an Object


Naming Fixed Objects

To rename a fixed object:

1.Display the object you want to rename in the fixed dimension box.

2.Select the fixed object.

3.Type the new object name in the formula bar (press backspace  to erase characters if you want to make corrections).

4.Press enter or click the enter box with the mouse.

  CA-Compete! enters the new object name in the fixed dimension box.

Note:	If you make a mistake, you can cancel any changes you have made to an object name by pressing escape or clicking the cancel box with the mouse before pressing enter.

Related topics:

CA-Compete! Procedures

Naming Dimensions

Naming Objects

Naming Displayed Objects

Delete an Object

Adding an Object


Naming Displayed Objects

In CA-Compete!, the formula bar is used for naming, data entry, and editing.

To rename an object:

1.Select the object you want to rename.

2.Type the new object name in the formula bar (press backspace to erase characters if you want to make corrections).

3.Press enter or click the enter box with the mouse.

Note: If you make a mistake, you can cancel any changes you have made to an object name by pressing escape or clicking the cancel box with the mouse before pressing enter.

Related topics:

CA-Compete! Procedures

Naming Dimensions

Naming Objects

Naming Fixed Objects

Delete an Object

Adding an Object


Changing the displayed object

By clicking the up/down arrows with the mouse, you can flip forward or backward through the objects (cards) on the dimension (the catalog box).  Once you have reached the end of the dimension another click will bring you around to the first object on the dimension.  You can think of the objects on a dimension as being arranged in a loop.

To advance forward through the list of objects on the dimension:

•Click the up/down arrows with the left mouse button.

To move backward through the list of objects on the dimension:

•Click the up/down arrows with the right mouse button.

Using the Object List to Change the Fixed Object:

You can use the dimension object list to change the fixed object.

To use the object list to change a fixed object:

1.Double-click the fixed object you want to change with the mouse.

2.From the object list that appears, select the object you want to make the fixed object.

3.Press enter.

CA-Compete! will display the selected object in the fixed dimension box.

Tip: When you have many objects on a dimension, it may be easier to change the fixed object by using the object list rather than the up/down arrows.

Related topics:

CA-Compete! Procedures

Changing a Views Orientation


Displaying all Objects on a Fixed Dimension

When you have finished renaming objects on a fixed dimension, you can check your work by displaying all the objects on the dimension.

To display all the objects on a fixed dimension:

•Double-click the object displayed in the fixed dimension box with the mouse.

CA-Compete! will display all the objects on the selected fixed dimension in a list box.

Related topics:

CA-Compete! Procedures

Changing the displayed object

Naming Objects

Naming Fixed Objects

Naming Displayed Objects

Delete an Object

Adding an Object

Positioning an object on a Dimension


Delete an Object

You may find you have too many objects on a dimension.  Or you may want to remove an object from a dimension because you no longer need the object and the data it represents in your model.  Removing objects from your models is a simple procedure in CA-Compete!.

To delete an object from a model:

1.Select the object you want to delete.

2.Choose Edit Delete.

CA-Compete! removes the selected object from the dimension; the cells defined by this object are also deleted from the model.

Tip: It is possible to delete several objects from a dimension at the same time.  First, create a selection on the dimension which includes the objects you want to delete.  Then, choose Edit Delete.  CA-Compete! deletes the selected objects from the dimension and the cells they define from the model.

Related topics:

CA-Compete! Procedures

Naming Objects

Naming Fixed Objects

Naming Displayed Objects

Displaying all Objects on a Fixed Dimension

Adding an Object

Positioning an object on a Dimension


Adding an Object

Often when you are building a new model, you will find you need more objects on a dimension than the default model provides.  In CA-Compete!, you can easily increase the number of objects on a dimension.

To add an object to a dimension:

1.Select an object on the dimension you want to expand.

2.Choose Edit  Insert or Edit Append.

The Edit Insert  command inserts an unnamed object before the object currently selected.  The Edit Append command inserts an unnamed object after the object currently selected.

Tip: It is possible to add more than one object at a time to a dimension.  First, among the objects already on the dimension, create a selection equal to the number of objects you want to add to the dimension. (That is, select three objects if you want to add three objects to the dimension.)  Then, choose Edit Insert or Edit Append.  The new objects will be added to the dimension before or after the selection of objects, depending on the command you use.

Related topics:

CA-Compete! Procedures

Naming Objects

Naming Fixed Objects

Naming Displayed Objects

Delete an Object

Adding a Dimension

Positioning an object on a Dimension


Adding a Dimension

If you need to include more than three sets of data in your model, add  another dimension to your model for each additional set of data you want to include.  A CA-Compete! model can have as many as twelve dimensions.

Whenever you add a dimension to a model,  you first name it and then determine the number of objects it will contain.  Objects on the new dimension must then be renamed.

To add a dimension to a model:

1.Select Edit Add Dimension.

2.Type the name of the new dimension in the Name box (press backspace to erase characters if you want to make corrections).

3.Type the number of objects you want the dimension to contain in the #Objects box.

4.Choose OK.

A fixed dimension box appears above the formula bar for each dimension you add.  The default object names are the dimension name followed by a number .

Related topics:

CA-Compete! Procedures

Naming Dimensions

Adding an Object

Adding a Dimension


Saving and Naming a Model

Once you have created the basic structure of your CA-Compete! model, you should save the file.

The Save command stores your model onto disk.  All the information you have entered in the model up until that point is written onto the disk, so that it can be recalled later.  Saving a model does not clear the screen, so you can continue working after you save.  Save your model often during a work session so that the changes you have made are not lost if the power supply is accidentally cut off, and save your work when you end your CA-Compete! session.

The first time you save a model, a dialog box  will appear which asks you to provide a name for the model.  CA-Compete! will store your model in a file with the name you provide.

To save a new model:

1.Select File Save.

2.Type the name of the file in the Save Model as box (press backspace to erase characters if you want to make corrections).

If you want to save the file in a different directory from the one displayed in the dialog box, include the directory path when you type the file name in the Save Model as box.

3.Choose OK.

You do not need to include the file extension with the name.  CA-Compete! provides the correct file extension (mdl) for you.

Note:	CA-Compete! limits model names to the MSDOS file name convention.

To save a file that has already been named:

•Select File Save.

CA-Compete! saves the file, but no dialog box appears.

Note:	If you want to rename a file, use the File Save As command.

Related topics:

CA-Compete! Procedures

Starting a Model

Open an Existing Model

Close a Model

Saving the Workspace


Open an Existing Model

When you save a new CA-Compete! model, you are asked to give the file a name.  Use this name to open the model during later work sessions.  For example, if you named a model practice.mdl and you want to work with it again, choose the File Open command and select practice.mdl in the dialog box.  CA-Compete! looks in the current drive and directory for the file name practice.mdl.

To open an existing CA-Compete! model:

1.Select File Open.

2.Choose the name of the file you want to open in the Files list box, or type the file name in the File Name box.

3.Select OK.

CA-Compete! closes the current file and opens the selected file in the CA-Compete! application window.  When the file opens, the application window displays the same configuration of view and sheet windows that you were working with when you last saved the model.

Related topics:

CA-Compete! Procedures

Starting a Model

Save and Name a Model

Close a Model

Saving the Workspace


Changing a Views Orientation

Two dimensions are always displayed in a view.  Objects on one dimension appear as row headings and objects on the other as column headings.  However, you may want to change the dimensions that are displayed in a view.  For instance, you may want to display the objects on a fixed dimension and fix an object on a displayed dimension.  Or you may want to exchange row and column headings with each other.   Changing the orientation of a view is easy in CA-Compete!.

To pivot two dimensions:

1.Select an object from one of the two dimensions you want to exchange.

2.Click the selected object with the right mouse button and hold down.

The pointer will turn into a flag that you can drag to a new position.

3.Position the flag so that it is centered over an object on the other dimension .

When the flag is positioned correctly it will change to reverse video.

4.Release the right mouse button to drop the flag on the object over which it is positioned.

CA-Compete! will switch the two dimensions.  If you pivot a fixed and a displayed dimension, all objects on the fixed dimension will be displayed and the object you moved from the displayed dimension will be a fixed object.  If you pivot two fixed dimensions, they will exchange places in the row of fixed dimensions.  If you pivot two displayed dimensions, the vertically displayed dimension will head the columns and the horizontally displayed dimension will head the rows instead.

Note:	You can also use the control+left mouse button for all actions assigned to the right mouse button in the procedure outlined above.

Related topics:

CA-Compete! Procedures

Changing the displayed object

Changing a Sheets Orientation


Changing a Sheets Orientation

You can also transpose the rows and columns in a sheet.  Then, the numbers that are usually row headings become column headings instead, and the alphabet characters that are usually column headings become row headings instead.  Any data stored in the sheet is also transposed.

To transpose the row and column headings in a sheet:

1.Select a row or column heading.

2.Click the selected heading with the right mouse button and hold down.

The pointer will turn into a flag that you can drag to a new position.

3.Position the flag so that it is centered over a heading on the opposite dimension .

When the flag is positioned correctly it will change to reverse video.

4.Release the right mouse button to drop the flag on the heading over which it is positioned

CA-Compete! will switch the row and column headings with each other, and all data in the sheet will also be transposed.

Note:	You can also use the control+left mouse button for all actions assigned to the right mouse button in the procedure outlined above.

Related topics:

CA-Compete! Procedures

Changing the displayed object

Changing a Views Orientation


Changing the Column Width

Object names, text,  and values may not be completely visible in a cell.  You can fully display an object name, text, or value by changing the column width.

The Format Column Width command lets you specify the width of the columns in a view or sheet as a number of characters.  The standard width of a CA-Compete! column is eight characters.

To change the width of a column using the keyboard:

1.Select a column or a cell in the column you want to change.

2.Select Format Column Width (a dialog box will appear).

3.Type the new column width in the Column Width box.

4.Choose OK.

To change the width of a column using the mouse:

1.Position the pointer over the vertical line at the right of the column heading.

2.A vertical bar with double arrows will appear.  Drag it to the right to widen the column or to the left to narrow the column.

Tip:	It is possible to change the width of several columns at once.   Select a row of column headings and follow either of the procedures listed above.

Related topics:

CA-Compete! Procedures

Hiding and Showing Objects


Hiding and Showing Objects

CA-Compete! lets you determine which objects you want to display on a dimension.  For example, if you plan to analyze the sales forecast for a company in your model, you may want to create a view that displays only future years on the YEARS dimension.

The Options Hide command lets you remove objects from the display.  Rows and columns disappear from view to make the display less crowded and more readable.  The hidden rows and columns may be brought back at any time with the Show command.

The Options Show command lets you display objects that you have previously hidden with the Hide command.  Rows and Columns reappear in the display.   Boths commands are available only when Full Menus are activated.

To hide an object from view:

1.Select the objects that you want to hide.

You may hide several objects from the rows dimension at once or several objects from the columns dimension at once, but you cannot hide objects from the two dimensions at the same time.

2.Choose Options Hide.

The objects and their rows or columns will disappear from the view.

To show hidden objects:

1.Select an object on the dimension that contains the hidden objects you want to display.

2.Choose Options Show.

A list box that displays the hidden objects on the dimension will appear.

3.Select the objects you want to show (press the shift+direction keys or shift+click with the mouse to extend the selection to more than one object).

4.Choose OK.

CA-Compete! will redisplay the selected objects in the view.

Note:	Only hide objects that are on a displayed dimension can be hidden.  However, you can show objects that have been hidden on either a fixed or displayed dimension.

Note:	Remember that the data for hidden objects is still in your model–no erasure has taken place–and is still used in any calculations that depend upon it.  You have merely organized your view, highlighting data for certain objects by hiding others.  If you copy and paste a range that includes hidden data, the hidden data will also be copied and pasted to the new location as well.

Related topics:

CA-Compete! Procedures

Changing the Column Width


Positioning an object on a Dimension

After you have created a structure and begun to enter data into a model, you may decide that you would like to change the position of the objects on a dimension.

To reposition objects on a dimension:

1.Select the object or group of objects you want to move and hold down with the right mouse button (or control+click with the left mouse button).

2.A flag will appear.  Drag it to the name of the object which you want the selected objects to precede.

3.When the flag turns to reverse video, release the flag.

CA-Compete! will reorder the objects and columns and rows of data on the dimension.

Related topics:

CA-Compete! Procedures

Naming Objects

Naming Fixed Objects

Naming Displayed Objects

Displaying all Objects on a Fixed Dimension

Delete an Object

Adding an Object


Naming a View or a Sheet

Formatted views and sheets can be stored as part of a model.  When you name a view or sheet, it is saved in its own window within the model.  All the format changes you have made to the view or sheet are saved as well–the orientation, hidden and displayed objects, column widths and row heights, fonts, alignment, borders, shading, number formatting, and data displayed in terms.  When you open a named view or sheet it appears in its own window, and the data is displayed in the same orientation and format as when you named it.   Every time you want to save changes to a view or sheet, use the Window Name Window command.

To name a view or sheet:

1.Make the window you want to name the active model window.

2.Select Window Name Window.

3.Type the view or sheet name into the Window Name box.

4.Choose OK.

The dialog box will disappear, and the title bar in the view or sheet window will display the window's new name.

Related topics:

CA-Compete! Procedures

Working with multiple windows

Close a View or a Sheet


Working with multiple windows

Each CA-Compete! model consists of an essentially unlimited number of views and sheets.  Since each view or sheet exists in its own window, it is possible to have several views and sheets open in the CA-Compete! application window at the same time.  All the windows displayed in the CA-Compete! application window are part of the same model.  Only one CA-Compete! model can be active in the application window at a time.

Opening a New Window

A new window can be a view or a sheet window.  When you open a new window, an untitled view or sheet appears in the CA-Compete! application window.  New views and sheets are formatted according to standard CA-Compete! settings.

To open a new view:

1.Select Window New Window.

2.Choose View.

3.Select OK.

To open a new sheet:

1.Select Window New Window.

2.Choose Sheet.

3.Select OK .

An untitled view or sheet will appear in the application window.

Opening a Named Window

After you have opened a file, you can open any named views or sheets you have saved as part of the model.

To open a named view:

1.Select Window Open Window.

2.Choose View in the Window dialog box.

3.Select the named view you want to open in the Named Views list.

4.Choose OK.

To open a named sheet:

1.Select Window Open Window.

2.Choose Sheet in the Window dialog box.

3.Select the named worksheet you want to open in the Named Sheets list.

4.Choose OK.

CA-Compete! will open and display the selected view or sheet in the application window.

Arranging Windows

Each model window you open becomes the active window and appears on top of the view or sheet previously displayed in the application window.    Even if the new window covers the other windows that were open, they have not been closed.  They are simply hidden behind the last window you opened.  All open views and sheets in a model are listed under the Window menu by name.

To activate an open view or sheet:

1.Select the Window menu.

2.In the Window menu list, Choose the view or sheet you want to make the active model window.

The view or sheet you select will appear on top of the other open windows in the CA-Compete! application window.

It is also possible to arrange open views and sheets so they are all visible in the application window at the same time.

To display open views and sheets together in the application window:

•Select Window Arrange All.

The open view and sheet windows will shrink and CA-Compete! will arrange them so that all the open model windows are visible in the application window.

Tip:	You can maximize a view or sheet window, so that it fills the whole CA-Compete! window, by clicking on the maximize arrow, selecting Maximize from the system menu, or double-clicking on the window title bar.

Related topics:

CA-Compete! Procedures

Naming a View or a Sheet

Close a View or a Sheet


Close a View or a Sheet

During a work session, you may want to close a view or sheet that you have opened.

To close an open model window:

1.Make the view or sheet you want to close the active model window.

2.Select the Control menu box of the window you have made active (this is the box containing a hyphen in the upper-left corner of the window's title bar).

3.Choose Close.

The view or sheet window will close but the model will remain open in the application window.  The view or sheet will no longer be listed under the Window menu.

Related topics:

CA-Compete! Procedures

Close a Model

Saving the Workspace

Naming a View or a Sheet

Working with multiple windows


Close a Model

When you complete a work session with CA-Compete! you will want to save the changes you made to the model and close the file.

To close a CA-Compete! model:

1.Select File Save.

2.Choose the CA-Compete! application window Control menu box  (this is the box containing a hyphen in the upper-left corner of the window's title bar).

3.Select the Close command.

Note:	If you try to close your model before saving your changes, CA-Compete! will ask if you want to save the changes you have made to the model.  If you have not made any changes to the model before since your previous save, CA-Compete! will close the model without asking if you want to save the changes.

Related topics:

CA-Compete! Procedures

Starting a Model

Open an Existing Model

Save and Name a Model

Saving the Workspace

Close a View or a Sheet


Saving the Workspace

Every time you save and close a CA-Compete! model, CA-Compete! automatically saves the workspace as well.  The next time you open the file, the CA-Compete! workspace will appear as it did when you last saved the model.

Related topics:

CA-Compete! Procedures

Starting a Model

Open an Existing Model

Save and Name a Model

Close a Model

Working with multiple windows


Entering data in a cell

To enter data in a cell, you must first select the cell to make it active.  When a cell is active, all actions are applied to the cell, and you can format or enter data in the cell.  You then type data for the active cell in the formula bar and enter the data in the cell.   In CA-Compete!, you use the formula bar for all naming, data entry, and editing.

To enter data in a cell:

1.Use the direction keys to select the cell where you want to enter data, or click with the mouse on the cell you want.

2.Type the data for the active cell in the formula bar (press backspace to erase characters if you want to make corrections).

3.Press enter or click the enter box with the mouse.

CA-Compete! enters the data in the cell!

If you make a mistake, you can cancel any data you have entered in the formula bar by pressing escape or clicking the cancel box with the mouse.  CA-Compete! will erase the data from the formula bar and the cell.

Tip: The formula bar is inactive whenever the enter and cancel boxes do not appear.  If you select a cell and begin typing or editing cell data, the formula bar becomes active.  You can also activate the formula bar by pressing F2 or clicking in the formula bar with the mouse.

Note:	Most of the commands on menus will be grayed when the formula bar is active.  Grayed commands are not available until you complete or cancel the entry in the formula bar.  If you are trying to work in a view or sheet and many of the commands you want to choose are grayed, check to make sure the formula bar is not active.

Related topics:

CA-Compete! Procedures

Entering data in a range

Change absulute and relative references

Referencing cells in a View

Referencing cells in a Sheet

Deleting Data


Entering data in a range

Once you have designated a range that includes the cells where you want enter data, you can quickly enter data in the view or sheet.

To enter data in a range:

1.Type the data for the first cell in the formula bar (press backspace to erase characters if you need to make a correction).

2.Press enter or click the enter box with the mouse to enter the data in the cell.

3.Press enter again to move to the next cell in the selection.

A value will appear in the first cell and the active cell will move down the first column to the bottom of the range.  If you continue to press enter the active cell will move to the top cell of the second column of the range, and so on until the range is full.

Note:	You can also use these procedures with multiple selections.  Use control+tab to move to the next area in a multiple selection and control+shift+tab to move to the previous area.

Related topics:

CA-Compete! Procedures

Entering data in a cell

Creating a range

Change absulute and relative references

Referencing a range

Deleting Data


Creating a range

Before entering any data, first designate the range in which you want to enter data.

To create a range:

1.Select the cell in the upper left-hand corner of the desired range.

2.While holding the shift key down use the direction keys to extend the selection to the group of cells you want in the range.

With the mouse hold the left mouse button down, and drag through the cells you want or shift+click on the cell in the lower, right-hand corner of the desired range.

Once you have selected a range, you can move among cells in the range and still keep the cells selected by using the keys in the following table.  Using the direction keys in a range will undo the selection.

Keystroke	Action

enter	Move down one cell in the selection

shift+enter	Move down one cell in the selection

tab	Move right one cell

shift+tab	Move left one cell in the selection

Related topics:

CA-Compete! Procedures

Entering data in a range

Creating a range

Change absulute and relative references

Referencing a range


Change absolute and relative references

You can use the Formula Reference command to quickly change the reference format of a cell to absolute, mixed, or relative format.  The Formula Reference command will switch the initial reference format of the object, column, or row selected to its opposite.

To change relative or absolute references:

1.Select the cell containing the reference you want to change.

2.In the formula bar, select the reference or references you want to change.

3.Choose Formula Reference or press F4 as many times as necessary to change the reference to the desired combination of absolute and relative references.

Note:	It is important to remember that the objects not displayed in underspecified references are relative references–even if the displayed objects have been made absolute.  To make an absolute reference to a specific cell in the database, you must insert an absolute sign ($) before every object that defines the cell.  You can do this by typing in the formula bar the objects that are currently not displayed in the reference and then inserting a dollar sign before each object in the reference.  You can also use the Formula Reference command to make an absolute reference to a specific cell in the database.  Select the reference you want to change in the formula bar and then choose Formula Reference.  CA-Compete! will add the missing objects that define the cell to the reference and switch the reference format of all the objects from relative to absolute.  Press enter to update the cell.

Related topics:

CA-Compete! Procedures

Referencing cells in a View

Referencing cells in a Sheet

Referencing a range


Referencing cells in a View

Every cell in a CA-Compete! structure is defined by a unique multidimensional description.  Since cells in the database are the intersection of individual objects from each of the dimensions in a model, they are referenced by the names of those intersecting objects and not alphanumeric coordinates.

A special syntax is used to refer to cells in a CA-Compete! structure.  The names of the intersecting objects from the model that define the cell are listed in a series and separated by periods.  Numeric object names such as years (1987, 1988, etc.) should be preceded by a single quotation mark (').  This quotation mark tells CA-Compete! that the following text is an object name and not a constant number.

CA-Compete! uses a shortened form of specification when referencing cells in formulas.  If the cell you reference in a formula shares objects with the cell in which you are entering the formula, you do not need to include these shared objects in the cell reference.  Underspecification means that a reference  includes only those objects that are different between the two cells in question, and not the objects they have in common.

When an address is underspecified, CA-Compete! automatically adopts an object for each of the missing dimensions  from the cell where you are typing the formula to complete any cell references during calculation.  These shared objects, however, are not displayed as part of the cell reference.

the two cells in question differ.

Underspecifying references can be a great time saver when you are entering formulas.  In short, cell references in CA-Compete! can have from one to twelve (if there are twelve dimensions in your model) objects, separated by periods.

Related topics:

CA-Compete! Procedures

Referencing cells in a Sheet

Referencing a range


Referencing cells in a Sheet

Since sheets are independent of the model database, they provide a scratchpad for your calculations and reports–they are do not use the view reference style.

Each column in a sheet has an alphabetic heading and each row a numeric heading.  Since sheet cells are the intersection of one row and one column,  they are referenced by their coordinates rather than object names.  Cell referencing in sheets follows the style A1, B1, C1, A2, B2, C2, and so on.

Related topics:

CA-Compete! Procedures

Referencing cells in a View

Referencing a range


Referencing a range

CA-Compete! uses the names of two corner cells to create a reference to a range.  The first half of a range reference is the name of the cell in the upper-left corner of the range, and the second half is the name of the cell in the lower-right corner of the range. These two names are listed together and separated by a colon.

Note:	Like cell references, a range reference can be absolute, mixed, or relative.  All rules and procedures that apply to absolute, mixed, and relative cell references apply to range references as well.

Related topics:

CA-Compete! Procedures

Referencing cells in a View

Referencing cells in a Sheet


Copy and Pasting Data

Often, you will want to enter the same values and formulas in several cells in the model.  An easy way to avoid duplicating effort, is to use CA-Compete!'s copy and paste features.

To copy and paste data from one group of cells to another:

1.Select the cell or group of cells that contain the values or formulas you want to copy.

2.Choose Edit Copy.

CA-Compete! stores the copied data in the clipboard.

3.Select the cell or group of cells where you want to paste the data.

4.Choose Edit Paste.

CA-Compete! pastes the data from the clipboard into the selected cells.

To move values or formulas from one group of cells to another:

1.Select the cell or range of cells that contain the values or formulas you want to move.

2.Choose Edit Cut.

The data disappears from the selection and stored in the clipboard.

3.Select the cell or range of cells where you want to place the data.

4.Choose Edit Paste.

The data will reappear in the new location you have selected.

Note:	If you copy and paste a range of data that includes hidden data, the hidden data will also be copied and pasted to the new location you have selected in the model.

Related topics:

CA-Compete! Procedures

Entering data in a cell


Deleting Data

CA-Compete! gives you several ways to delete data you have entered in a cell, range, or multiple selection.

To delete data from a cell or group of cells:

1.Select the cell or group of cells that contain the data you want to delete from the model.

2.Choose Edit Clear or press the delete key.

3.Select the Formulas option button.

4.Choose OK.

CA-Compete! erases the data in the cell, range, or multiple selection.

Note:	You can also use the Edit Clear command or the delete key to remove formats and notes in the selection.  If you choose the option All in the Edit Clear dialog box, CA-Compete! will remove all data, formats and notes in the selection.

Related topics:

CA-Compete! Procedures

Entering data in a cell

Entering data in a range


Defining Formula Scope

Every value or formula you enter, whether it is a global or a worksheet formula, has a certain scope.  The scope of a formula tells CA-Compete! where the value or formula is available for use in a model.

For instance, if the scope of a sales formula is limited to one cell, then that is the only cell in the model where the formula is available for use.  If the scope includes all Sales cells in the model, then the formula is available for use in every cell in the model that is defined by the object Sales.

Note:	Scope determines only where it is possible to use the formula in a model.  Before you can use any global formula you create, you must first apply the formula to a selection of cells.  Worksheet formulas are automatically applied to the active cell when you enter the formula in the formula bar.

Related topics:

CA-Compete! Procedures

Expanding Formula Scope

Shrinking Formula Scope

Applying Global Formulas


Expanding Formula Scope

Any worksheet formula can be changed to a global formula, by expanding its scope.  Before you can expand a formula's scope, however, Full Menus must be activated.

To expand formula scope:

1.Select the cell that contains the formula whose scope you want to expand.

2.Choose Formula Formula Scope.

A dialog box appears in which you can change the formula's scope:

3.In the Worksheet Dimension box, select the object that belongs to the dimension on which you wat the formula to be available for all object.

When an objected is highlighted, CA-Compete! displays the dimension it is on below the Worksheet Dimension box.

4.Click the "«" button with the mouse.

5.Repeat steps 4 and 5 for each dimension on which you want the formula to be available for all objects.

6.When you are finished shifting dimensions to the Global Dimension box, choose OK.

If you make a mistake shifting objects to the Global Dimension box, select the dimension in the Global Dimensions box that you want to shift back to the Worksheet Dimensions box.   Click the "»" button with the mouse.  In the list that appears, choose the object you want to reappear in the Worksheet Dimension box.

Note:	Expanding the scope of a formula does not affect cells where the value or formula is already applied.  The number of cells in the model where the formula is already active does not change either.  When you transform a worksheet formula into a global formula, you do not enter the formula in any other cells in the model; you only redefine the scope the formula.

Related topics:

CA-Compete! Procedures

Defining Formula Scope

Shrinking Formula Scope

Applying Global Formulas


Shrinking Formula Scope

You can also change the scope of a global formula so that it is available in fewer cells in a model.   Before you can shrink a formula's scope, however, Full Menus must be activated.

To shrink the scope of a global formula:

1.Select a cell where the formula whose scope you want to change is applied.

2.Choose Formula Formula Scope.

The Set Formula Scope dialog box will appear:

3.To limit formula scope to a single object on a dimension where it is currently true for all objects, select the dimension in the Global Dimension box.

4.Click  the "»" button with the mouse.

5.In the dimension object list that appears, select the object for which you want the formula to be true.

6.Repeat steps 3 through 5 until you have finished deleting the dimensions you want from the Global Dimensions box and entering the objects you want in the Worksheet Dimensions box.

7.Choose OK.

A new formula scope is defined, and the selected formula will be available only in cells that are included in this smaller range.

Note:	Whenever you shrink the scope of a formula, CA-Compete! removes the formula from the cells where it has been applied that are no longer included in the formula scope.

Related topics:

CA-Compete! Procedures

Defining Formula Scope

Expanding Formula Scope

Applying Global Formulas


Applying Global Formulas

Before you can use a global formula in the model, you must apply the formula to cells in the multidimensional range defined by the formula's scope.

Distinguish:

Applying Global Formulas to cells

Applying Global Formulas to a Range

Applying Global Formulas to its total Scope

Related topics:

CA-Compete! Procedures

Update a Global Formula

Overwrite a Global Formula

Clear a Global Formula

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode


Applying Global Formulas to cells

To apply a global formula to a cell:

1.Select the cell where you want to use the global formula.

2.Choose Formula Apply Formula.

The Apply Formula dialog box appears.

3.Select the Global Formula option button.

4.From the global formula list that appears, choose the formula you want to use in the cell.

5.Choose Apply.

6.Select OK.

A calculated value will appear in the selected cell.  Select the cell to display the active global formula in the formula bar.

Related topics:

CA-Compete! Procedures

Applying Global Formulas

Applying Global Formulas to a Range

Applying Global Formulas to its total Scope

Update a Global Formula

Overwrite a Global Formula

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode


Applying Global Formulas to a Range

It is also possible to apply a global formula to a range of cells that is included in the formula scope.  You can use the Formula Apply Formula command to apply a global formula to several cells at the same time.

To apply a global formula to a range of cells:

1.Select the range of cells where you want to apply the global formula.

2.Choose Formula Apply Formula.

3.Select the Global Formula option button.

4.From the global formula list that appears, choose the formula you want to use in the selected cells.

5.Choose Apply.

6.Select OK.

Note:	When a range is selected and you use the Formula Apply Formula command, the list that appears in the dialog box includes the possible formulas for all cells in the range.  Although these formulas appear together, they are not all available in every cell.  If you choose to apply a formula whose scope does not include every cell in the selected range, CA-Compete! will apply the chosen formula only to those cells that are included in the selected formula's scope.

Tip:You can apply as many formulas in a range as you like during one work session with the Apply Formula dialog box.  After you have selected and applied one global formula, do not select OK.  Choose another formula from the Global Formula list box and select Apply again.    CA-Compete! will activate each formula you choose in the cells that are a part of its scope.  When you have finished applying formulas, select OK.

Related topics:

CA-Compete! Procedures

Applying Global Formulas

Applying Global Formulas to cells

Applying Global Formulas to its total Scope

Update a Global Formula

Overwrite a Global Formula

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode


Applying Global Formulas to its Entire Scope

Sometimes you may want to apply a global formula to every cell that is included in the formula scope.

To apply a global formula to the multidimensional range defined by its scope:

1.Select a cell where the global formula you want to apply is active.

2.Choose Formula Formula Scope.

3.Change the scope of the selected formula if you want.

4.Select the Apply option box.

An X will appear in the option box; this means the option is active.

5.Choose OK.

CA-Compete! will apply the global formula to every cell included in the formula scope.

Note:	A counter in the reference area of the formula bar indicates when CA-Compete! has finished setting a global formula.  Because CA-Compete! performs its calculations in the background, you can continue to work while CA-Compete! applies a global formula.

Related topics:

CA-Compete! Procedures

Applying Global Formulas

Applying Global Formulas to cells

Applying Global Formulas to a Range

Update a Global Formula

Overwrite a Global Formula

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode


Update a Global Formula

Often, you will want to change a global formula you have entered in your model.  CA-Compete! lets you modify global formulas easily.

To update a global formula:

1.Select a cell where the global formula you want to modify is applied.

2.Press F2 or click in the formula bar with the mouse to enter Edit mode.

3.Make the changes you want to the formula.

4.Press enter or click the enter box with the mouse.

A dialog box appears.

5.Choose the Update global formula option button.

6.Select OK.

CA-Compete! rewrites the global formula, and the modified formula is entered in any cells where the formula is already applied.  CA-Compete! recalculates these cells on the basis of the updated formula.

Related topics:

CA-Compete! Procedures

Applying Global Formulas

Overwrite a Global Formula

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode


Overwrite a Global Formula

Within a range where you have applied a global formula, sometimes you will want to calculate certain cells differently from the others.  CA-Compete! lets you overwrite global formulas you have applied to a selection of cells without modifying the global formula or its scope.

To overwrite a global formula in a cell:

1.Select the cell which contains the global formula you want to overwrite.

2.In the formula bar, type the new data you want entered in the cell.

3.Press enter or click the enter box with the mouse.

The Overwrite Global Formula dialog box appears:

4.Choose the Enter worksheet formula option button.

5.Select OK.

CA-Compete! writes a new worksheet formula and applies it to the selected cell.  The global formula previously active in the cell and its scope remain unchanged.  It is still available for you to use in the cell if you change your mind.

Note:	Whenever you apply a global formula to a cell where you have entered a Worksheet Formula,  CA-Compete! overwrites the worksheet formula and stores it in the model database.  You can reapply worksheet formulas to a cell or group of cells by using the Formula Apply Formula command.  Follow the same procedures you would use to apply a global formula to a cell or range, but select the Worksheet Formula option button in the Apply Formula dialog instead of the Global Formula option button.

Related topics:

CA-Compete! Procedures

Applying Global Formulas

Update a Global Formula

Clear a Global Formula

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode

Deleting a Global Formula from a Model


Clear a Global Formula

You can clear global formulas from a selection just as you would clear data from a cell or range.

To clear a global formula from a selection:

1.Select the cell or group of cells which contain the global formulas you want to delete from the view.

2.Choose Edit Clear or press delete.

3.Select the Formulas option button.

4.Choose OK.

CA-Compete! will erase all formulas, worksheet and global, from the selected cells or range.

Note:	When you clear a global formula from a cell or range, you do not delete the global formula from the model database:  you only undo its application in the selection.  To delete a global formula from the model, you must delete the formula from the global cell where it is entered in the model database.

Related topics:

CA-Compete! Procedures

Applying Global Formulas

Update a Global Formula

Overwrite a Global Formula

Clear a Global Formula

Deleting a Global Formula from a Model


Create a term

To create a new term:

1.Select Formula Set Term or double-click on the Terms box with the mouse.

A dialog box will appear:

The term that CA-Compete! is currently using as a unit of measure will be highlighted in the Selected Term list box.

2.Choose Options.

3.Select Add.

A new term is added to the list box, and CA-Compete! provides a default name for the term.

4.Type the name you have chosen for the new term in the Term box.

5.Choose Rename or press enter.

6.Select OK.

The name of the new term you created and selected in the Terms dialog box will appear in the Terms box in your view.

Note:	Although you have just added a new term to the list of terms you can use to display data in your model, you have not yet defined the new term as a specific unit of measure.  Defining a term is similar to writing a formula in a view or sheet.

Related topics:

CA-Compete! Procedures

Defing how a term is applied

Assigning a format to a term

Defining term Formulas

View Data in a Term

Enter Data in a Term


Defing how a term is applied

When you create a new term, it need not be applied to all objects.  To apply a term selectively, you have two options:  inclusion (specifying the objects that will be displayed in the new term) and exclusion (specifying the objects that will not  be displayed in the new term).  If no objects have been specified as either inclusions or exclusions, CA-Compete! applies the selected term to all data in the model.

Exclusions

An exclusion is an object that should not be displayed in the unit of measure defined by the term.  For example, you would not want to express data about the number of units sold as a percentage of sales.

To specify objects that should not be displayed in terms of the unit of measure you have created:

1.Choose Formula Set Term or double-click on the Terms box and then, in the dialog box that appears, select the term for which you want to specify a list of exclusions.

2.If the Options section of the Terms dialog box is not visible, choose Options.

3.Select the Exclude option button.

4.Choose Change.

A second dialog box will appear:

The Exclusions dialog box lists the objects which contain data that will not be displayed in the unit of measure defined by the selected term.  If no exclusions or inclusions have been specified, the list will be empty and all data in the model will be displayed in the unit of measure defined by the selected term whenever it is applied.

5.To add an exclusion to the list, choose Add.

6.In the object selector that appears, select the objects you want to exclude from use with the term, and choose OK after each selection.

7. When you have finished adding objects to the exclusion list, select Close, and the object selector will disappear.

8.In the Exclusions dialog box, choose OK.

9.In the Terms dialog box, select OK.

The name of the new term you created and selected in the Terms dialog box will appear in the Terms box in your view.  Any data in the model that is defined by the objects in the Exclusions list will be displayed in No Term.

Inclusions

An inclusion is an object that should be displayed in the unit of measure defined by the term.  For example, you may want to express actual data (but no other type of data) in terms of its variance to budget data.  In this case, you would specify the object Actual as an inclusion.

To specify objects that should be displayed in terms of the unit of measure you have created:

1.Choose Formula Set Term or double-click on the Terms box and then, in the dialog box that appears, select the Term for which you want to specify a list of inclusions.

2.If the Options section of the Terms dialog box is not visible, choose Options.

3.Select the Include option button.

4.Choose Change.

A second dialog box will appear:

The Inclusions dialog box lists the objects which contain data that will be displayed in the unit of measure defined by the selected term. If no inclusions or exclusions have been specified, the list will be empty and all data in the model will be displayed in the unit of measure defined by the selected term whenever it is applied.

5.To add an inclusion to the list, choose Add.

6.In the object selector that appears, select the objects you want to include for use with the term, and choose OK after each selection.

7. When you are finished adding objects to the inclusions list, select Close, and the object selector will disappear.

8.In the Inclusions dialog box, choose OK.

9.In the Terms dialog box, select OK.

The name of the new term you created and selected in the Terms dialog box will appear in the Terms box in your view.  Any data in the model that is not defined by the objects in the Inclusions list will be displayed in No Term.

Related topics:

CA-Compete! Procedures

Create a term

Assigning a format to a term

Defining term Formulas

View Data in a Term

Enter Data in a Term


Assigning a format to a term

You may find that you want to assign a special format to a term to highlight or clarify the unit of measure defined by the term.  For example,  you might want data displayed in terms of percentage of sales to have the built-in CA-Compete! format 0.00%.  It is also possible to format data expressed in a particular term so that it appears in a special color.  Formats like these are helpful if certain data in your model are excluded from use with a term or if a limited portion of data is included for use with a term, and you want to avoid mistaking data that are expressed differently from most data in the model.

To assign a format to a term:

1.Choose Formula Set Term, or double-click on the Terms box with the mouse.

2.In the dialog box that appears, select the term to which you want to assign a format from the Select Term list box.

3.If the Options section of the Terms dialog box is not visible, choose Options.

The Format section of the Terms dialog box lists the current format assigned to the term.  The default format for each term is the built-in CA-Compete! format General.

4.In the Format section of the dialog box, select Change.

The Format dialog box will appear:

5.Select the format you want to assign to the term from the Format Number list, or type the desired format in the Format box.

6.Choose OK.

7.In the Terms dialog box, select OK.

The name of the term you selected in the Terms dialog box will appear in the Terms box and all data expressed in the unit of measure defined by the term will have the selected format.

Related topics:

CA-Compete! Procedures

Create a term

Defing how a term is applied

Defining term Formulas

View Data in a Term

Enter Data in a Term


Defining term Formulas

In addition to naming a new Term, you must also enter the formula that will convert data in the model to the unit of measure defined by the term.

You enter term formulas in the formula bar just as you would a worksheet or global formula.  A term formula can include the variable X (where X represents every cell in the analysis) in addition to one or more arithmetic operators (+ , - , / , * , %, ^), built-in CA-Compete! functions, constants, and cell and object references.

Each object or cell in a term formula should be referenced in the CA-Compete! style.  For example, if the data entered in your model is expressed in dollars and the term you have created is $/Unit, then the term formula would be:  X/Units.

To define a new term formula or to redefine an existing one:

1.Select Formula Set Term, or double-click with the mouse on the Terms box.

2.Choose the term you want to define from the list of terms in the Terms dialog box, or create a new term and select it.

3.Choose OK.

The selected term appears in the Terms box.  If it is already defined, the data in the model will be displayed in that unit of measure the term.

4.Use the direction keys to move to and select the Terms box to make it active, or click with the mouse on the Terms box.

5.Type an equal sign in the formula bar.

6.In the formula bar, enter the term formula that should be used to convert data (press backspace to erase characters if you need to make a correction).

To enter object and cell references in the formula bar, you can type, click with the mouse on the object or cell you want to reference, or use the object selector.

7.Press enter or click the enter box with the mouse.When you enter the term formula, CA-Compete! will convert the data in the model that is specified for use with the term into the unit of measure the term formula defines.

Note:	Terms can also be relative.  In other words, you can write a term formula so that the data in the model will be expressed in that term only if it meets the criteria defined by the term formula.

Related topics:

CA-Compete! Procedures

Create a term

Defing how a term is applied

Assigning a format to a term

View Data in a Term

Enter Data in a Term


View Data in a Term

You may wish to view your data in several different measures to complete your analysis or to make more accurate comparisons.  CA-Compete!'s terms feature enables you to view your data in different ways without creating new models.  Using terms, you can perform many types of data analysis in one model.

To view data in terms:

1.Select Formula Set Term, or double-click with the mouse on the Terms box.

2.From the list in the Terms dialog box, choose the term in which you want to view data in your model.

3.Select OK.

The name of the selected term will appear in the Terms box, and the specified data in the model will be converted to the unit of measure defined by the term.

Note:	Remember, a term simply enables you to view the data stored in the model in a different way.  The values and formulas entered in each cell remain unchanged.  If you select a cell in a view where a term is applied, you will notice the display in the formula bar differs from the display in the cell itself.  The value or formula actually stored in the cell is displayed in the formula bar, while the cell itself shows the equivalent unit of measure defined by the currently active term.

Related topics:

CA-Compete! Procedures

Create a term

Defing how a term is applied

Assigning a format to a term

Defining term Formulas

Enter Data in a Term


Enter Data in a Term

It is also possible to use terms to enter data in a model.  When a term is applied and you enter a value in a cell, CA-Compete! does not store this value in the model database but instead enters in the active cell a formula that is derived from the term formula.  You can not use terms to enter data in a model unless the term formula is reversible.  A term formula is reversible if it contains only one variable and has arithmetic operators.  If the term formula contains a built-in CA-Compete! function, it is not reversible and you cannot enter data in cells where this term is applied.

For example, the term %Sales, which calculates the data in each cell of your model as a percentage of sales (X/Sales*100), is a reversible formula.  If you enter the value 25 (meaning 25%) in a cell for production costs when %Sales is the active term, Compete! will enter the formula 25/100*Sales in that cell and store this formula, not the value 25, in the model database.  The Terms feature simply enables you to quickly apply one conversion formula to data throughout a model.  Data in the model is still stored as it was entered in the model.  Therefore, data entered in terms will be stored in the model as a formula and not a value.

To enter data in terms:

1.Select Formula Set Term, or double-click with the mouse on the Terms box.

2.From the list in the Terms dialog box, choose the term in which you want to enter data.

3.Select OK.

4.Use the direction keys to move to and select the cell where you want to enter data, or click with the mouse on the cell you want.

5.Enter the data you want in the formula bar in the unit of measure defined by the active term

6.Press enter, or click the enter box with the mouse.

The value you entered in the formula bar will be incorporated into the formula created and entered in the selected cell.

Note:	You can write formulas yourself when working in terms.  Formulas that you enter in cells while working in terms are stored in the model database just as they would be when you are working in No Term.  CA-Compete! applies term formulas to the resulting value of calculated cells to display the data in the model in terms.  Values entered in cells that are excluded from the application of a term will be stored in the database as they are entered in the formula bar.  You should enter these values in the unit of measure in which you want them to be displayed when No Term is applied.

Tip:You can also use the CA-Compete! Tools commands when working in terms.  You can allocate, forecast, goal seek, and create response functions in terms.

Related topics:

CA-Compete! Procedures

Create a term

Defing how a term is applied

Assigning a format to a term

Defining term Formulas

View Data in a Term


Exiting Global Mode

To change a global dimension back to a worksheet dimension:

•Click the red globe icon of the global dimension you want to change back to a worksheet dimension with the mouse.

The red globe icon will change from to a green cell icon.  If you change a displayed global dimension back to a worksheet dimension, the objects on the dimension will reappear.  If you change a fixed global dimension back to a worksheet dimension, an object will again be displayed in the fixed dimension box.

Tip:If you select an object on a worksheet dimension, the dimension name will be displayed in the status bar.

Related topics:

CA-Compete! Procedures

Switch To Global Mode

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode

Deleting a Global Formula from a Model


Switch To Global Mode

To change a worksheet dimension to a global dimension and enter global mode:

1.Select Options Workspace.

2.Turn on the Global icons option (an "X" appears in the Global icons option box when the option is turned on).

3.Choose OK.

4.Click the cell icon of the dimension you want to globalize with the mouse; the green cell icon will change to a red globe icon  when the dimension becomes global.

Whenever you enter global mode, the workspace changes.

You can think of the three-dimensional model illustrated above as a cube. When all three dimensions are global, the global cell in the workspace represents this entire "cube" of data.

Related topics:

CA-Compete! Procedures

Exiting Global Mode

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode

Deleting a Global Formula from a Model


Writing Global Formulas in Global Mode

Entering a global formula in a global cell is similar to entering a value or formula in a worksheet cell.  Before you can enter a global formula directly into a model, however, you must define the formula's scope.  To do this, you must configure the global and worksheet dimensions in the model so that the global cell that is displayed represents the multidimensional range you want to include in the formula's scope.

To enter a global formula in a global cell:

1.Globalize the dimensions on which you want the formula to be available for all objects; the remaining dimensions will be worksheet dimensions, on which you want to specify the objects for which the formula is available.

The status bar will display the global mode indicator.

2.Use the direction keys to move to and select the global cell where you want to enter the formula or click with the mouse on the global cell you want.

3.Type an equal sign in the formula bar.  (CA-Compete! will insert the equal sign if you begin a global formula, by pasting a function in the formula bar.)

4.In the formula bar, enter the functions, references, values, and operators you want to include in your formula (press backspace to erase characters if you want to make corrections).

You can type the formula, use the Enter and Point modes, or use the object selector to enter a formula in the formula bar.

5.Press enter, or click the enter box with the mouse.

The global formula will be displayed in the formula bar and in the global cell as text.  No values are calculated in global cells.

Note:	Entering a formula in a global cell does not make it active, instead,, you are simply assigning a formula to a multidimensional range represented by the global cell.  To activate the global formula in a worksheet cell, you must apply it to the worksheet cell you want.

Related topics:

CA-Compete! Procedures

Switch To  Global Mode

Exiting Global Mode

Applying Global Formulas in Global Mode

Deleting a Global Formula from a Model


Applying Global Formulas in Global Mode

To use a global formula in your model, you must apply it to a selection of worksheet cells.  By using the Formula Apply Formula command while working in global mode, you can quickly and easily apply a global formula in all or specific areas of the formula scope.

Applying a Global Formula Throughout Its Entire Scope

To apply a global formula to every cell in the formula scope:

1.Configure the global and worksheet dimensions so that the global cell that defines the formula's scope is displayed in the view.

2.Use the direction keys to move to and select the global cell that contains the formula you want to apply, or click with the mouse on the global cell you want.

3.Choose Formula Apply Formula.

4.In the Apply Formula dialog box that appears, select the Global Formula option button.

5.From the global formula list that appears, choose the formula you want to apply.

6.Select Apply.

7.Choose OK.

A counter in the reference area of the formula bar indicates when the global formula has been set throughout its scope.  If you return to worksheet mode, you will see that CA-Compete! activated the global formula in every cell included in the formula scope.  A calculated value appears in each of these cells.

Tip:To move quickly from the view where you are working to the global cell that contains the formula you want to apply, select a worksheet cell where the global formula is applied and press F8.  CA-Compete! will enter global mode and display the global cell that contains the global formula you want to apply.  To return to worksheet mode and the view where you were working, press F8 again.

The F7 key is a useful accelerator for the Formula Apply Formula command when you are working in global mode.  If you press F7 when a global cell is selected, CA-Compete! applies the global formula it contains throughout the entire formula scope.  If you press F7 when a worksheet cell is selected, the worksheet formula is applied to the cell, and any global formulas it contains are overwritten.

Note:	Be cautious about applying a global formula throughout its entire  scope, since you may unintentionally overwrite worksheet or other global formulas you want to use.   Instead, you should apply global formulas as needed in cells or ranges when working with a model.

Limiting the Application of Global Formulas in Global Mode

You can limit the application of a global formula to specific areas of its scope.  To apply global formulas to cells in small regions of the entire formula scope, you can select cells or ranges while in worksheet mode and use the Formula Apply Formula command.  It is also possible, however, to apply a global formula to specific areas of its scope while working in global mode.

To limit the application of a global formula while in global mode:

1.Select the global cell where the global formula is entered.

2.Change one or more of the global dimensions to a worksheet dimension.

3.Select the global cell or cells that represent the range or ranges where you want to apply the global formula in the model.

4.Choose Formula Apply Formula.

5.Select the Global Formula option button.

6.From the Global formula list that appears, choose the formula you want to apply.

7.Select OK.

The counter in the reference area of the formula bar will indicate when CA-Compete! has finished applying the selected formula in the ranges represented by the selection of global cells.  When you return to worksheet mode, the cells included in these ranges will contain the global formula you selected.

Related topics:

CA-Compete! Procedures

Applying Global Formulas

Switch To Global Mode

Exiting Global Mode

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode


Deleting a Global Formula from a Model

To delete a global formula from a model:

1.  Select a worksheet cell where the global formula you want to delete is applied and press F8, or globalize the dimensions for which the global formula is true (these dimensions are listed in the status bar when a worksheet cell that contains an active global formula is selected).

CA-Compete! will enter global mode and display the global cell that contains the global formula you want to delete.

2.Select the global cell that contains the global formula you want to delete.

3.Choose Edit Clear or press delete.

4.Select the Formulas option button.

5.Choose OK.

CA-Compete! deletes the global formula from the model and erases the formula from cells where it was applied in the model.

Tip:To return to worksheet mode and the view where you were working, press F8.

Related topics:

CA-Compete! Procedures

Clear a Global Formula

Writing Global Formulas in Global Mode

Applying Global Formulas in Global Mode


Creating Links between Applications

Using Edit Copy and Edit Paste Link

The easiest way to create a DDE link between CA-Compete! and other Windows applications is to use the Edit Copy and Edit Paste Link commands to enter remote references in an application automatically.  The Edit Paste Link command is available only when Full Menus are activated.

See also:

Importing Data Into CA-Compete!

Exporting Data from CA-Compete!

Creating Links Manually

The Edit Copy and Edit Paste Link commands are convenient to use with applications that allow you to select the data you want to link.  However, you may need to type a remote reference in the formula bar to access some types of data from CA-Compete! – for instance, an SQL query to a Q&E database.  When using CA-Compete! as a client, you can create a DDE link yourself by typing the appropriate reference in the formula bar and entering it in a cell.

See also: The extern functions:

EXTERN$() and EXTERN()

Related topics:

CA-Compete! Procedures

Turning CA-Compete! DDE Links off and on

Disable DDE Requests


Importing Data Into CA-Compete!

To import data from another Windows application into CA-Compete!:

1.Open the CA-Compete! model and the server application.

2.Activate the window that contains the data you want to import into CA-Compete!.

3.Cut or copy the data you want to import, using the procedures for cutting or copying in the server application.

4.Activate the CA-Compete! window.

5.Select the cells where you want to paste the linked data.

6.Choose Edit Paste Links.

The imported data appears in the selected CA-Compete! range and remote references are automatically entered in the selected CA-Compete! cells.  These cells are now hot linked to the server application, and they will be updated any time data in the server application changes.

Tip:If the Edit Paste Link command in CA-Compete! is grayed after you have cut or copied data in another application, it means that this application does not support DDE.

Related topics:

CA-Compete! Procedures

Creating Links between Applications

Exporting Data from CA-Compete!

Turning CA-Compete! DDE Links off and on

Disable DDE Requests


Exporting Data from CA-Compete!

You can also use the Edit Copy and Edit Paste Link commands to link other Windows applications to data entered in a CA-Compete! model.

To export data from CA-Compete! to another Windows application:

1.Open both CA-Compete! and the client application.

2.Activate the CA-Compete! window that contains the data you want to export.

3.Select the data you want to export.

4.Choose Edit Cut or Edit Copy.

5.Activate the window where you want to import this data.

6.Paste the data, using the procedure for pasting links in the application to which you are exporting.

The application to which you paste linked the CA-Compete! data will now make remote requests to CA-Compete! for data updates.

Note:	When you are exporting data from CA-Compete! to another Windows application, the Edit Cut command acts the same as the Edit Copy command.  That is, CA-Compete! does not delete data from the cut range when you paste or paste link data in the destination application.

Related topics:

CA-Compete! Procedures

Creating Links between Applications

Importing Data Into CA-Compete!

Turning CA-Compete! DDE Links off and on

Disable DDE Requests


Turning CA-Compete! DDE Links off and on

It is possible to turn the DDE links off so that data imported into your CA-Compete! model from other Windows applications will not be updated.

To turn DDE links off:

1.Choose Options Calculation.

2.Unselect the option Update Remote References (the X should disappear from the check box).

When the Update Remote References option is turned off, CA-Compete! will not respond to information updates from its server applications.

To turn DDE links on:

1.Choose Options Calculation.

2.Select the option Update Remote References (an X should appear in the check box).

When the Update Remote References option is turned on, CA-Compete! will respond to information updates from its server applications.

Related topics:

CA-Compete! Procedures

Creating Links between Applications

Importing Data Into CA-Compete!

Exporting Data from CA-Compete!

Disable DDE Requests


Disable DDE Requests

If your CA-Compete! model is a server application, it responds to any DDE requests from other Windows applications.  As you are working, you may want to turn off CA-Compete!'s automatic response to these requests.

To disable remote requests:

1.Choose Options Workspace.

2.Select the Ignore Remote Requests option (an X should appear in the check box).

When the Ignore Remote Requests option is turned on, the model will not respond to requests for information from client applications.  In addition, it is not possible to use the Paste Link feature to create new links between the model and other DDE-compatible applications.

To re-enable remote requests:

1.Choose Options Workspace.

2.Unselect the Ignore Remote Requests option (the X should disappear from the check box).

When the Ignore Remote Requests option is turned off, the model will respond to requests for information from client applications.  You can also create links between the model and other DDE-compatible applications.

Related topics:

CA-Compete! Procedures

Creating Links between Applications

Importing Data Into CA-Compete!

Exporting Data from CA-Compete!

Turning CA-Compete! DDE Links off and on