Formula Operators and Functions
SpreadTM
Notices Information in the documentation is subject to change without notice and does not represent a commitment on the part of FarPoint Technologies, Inc. The software described in this document is furnished under a license or nondisclosure agreement. The software may be used or copied only in accordance with the terms of the agreement. It is against the law to copy this software on any medium except as is specifically allowed in the license or nondisclosure agreement. No part of the documentation may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or information storage and retrieval systems, for any purpose without the express written permission of FarPoint Technologies, Inc. © 1998, 2002 FarPoint Technologies, Inc. All rights reserved. Unless otherwise noted, all names of companies, products, street addresses, and persons contained herein are part of a completely fictitious scenario or scenarios and are designed solely to document the use of a FarPoint Technologies, Inc., product. Spread and List Pro are trademarks of FarPoint Technologies, Inc. ActiveX, AppStudio, FoxPro, Microsoft, Visual Basic, Visual C++, Visual InterDev, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Intel and Pentium are registered trademarks of Intel Corporation. Borland is a registered trademark and Resource Workshop is a trademark of Borland International, Inc. TrueType is a registered trademark of Apple Computer, Inc. Adobe, Acrobat, and FrameMaker are registered trademarks of Adobe Systems Incorporated. WebWorks Publisher is a registered trademark of Quadralay Corporation. Other brand and product names are trademarks or registered trademarks of their respective holders. The documentation was produced using Adobe® FrameMaker® 6. The online help file was produced using WebWorks Publisher® 2000. PDF documents were produced using Adobe Acrobat® 5.0.
Distribution Restrictions If you are using the trial or evaluation version of this product, then you may not distribute any of the files provided with the trial or evaluation version. The control DLL’s and OCX’s distributed with the retail product may be distributed by the user royalty free. Distribution by the user of any designers or design-time assistants (EXE’s or DLL’s), executables, source code, or license files (.LIC) distributed by FarPoint Technologies as part of this product is prohibited. You shall not develop applications that provide an application programmable interface to the Software or the Software as modified. Redistribution by your users of FarPoint Technologies’ DLL’s and OCX’s, or your modified or wrapped version of our OCX’s without the appropriate redistribution license from FarPoint is prohibited.
Table of Contents Formula Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Formula Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Data Types for Formula Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 ABS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 ACOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 ACOSH. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 ADD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 AND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 ASIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 ASINH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 ATAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 ATAN2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 ATANH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 AVERAGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 CEILING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 CHAR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 CLEAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 CODE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 COMBIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 CONCATENATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 COS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 COSH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 DATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 DAY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 DB. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 DDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 DEGREES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 EVEN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 EXACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 EXP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 FALSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 FIND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 FLOOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 FV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 HOUR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 IF. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 INT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
3
Table of Contents
INVERSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ISBLANK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ISEVEN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ISNONTEXT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ISNUMBER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ISODD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ISREF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ISTEXT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LEFT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LEN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LOG10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LOWER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MAX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MEDIAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MIN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MINUTE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MODE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MONTH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . NEG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . NOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . NOW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . NPER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ODD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . OR. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PERMUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PMT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . POWER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PRODUCT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PROPER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . RADIANS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . RAND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . RANK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . REPLACE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . REPT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . RIGHT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4
25 25 25 26 26 26 27 27 28 28 29 29 29 30 30 30 31 31 32 32 33 33 34 34 34 35 36 36 36 37 37 38 38 39 39 40 40 41 42 42 43
Table of Contents
ROUND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ROUNDDOWN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ROUNDUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SECOND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SIGN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SINH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SLN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQRT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQUARE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . STDEV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . STDEVP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SUBSTITUTE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SUM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SUMSQ. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SYD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TANH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TIME. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TODAY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TRIM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TRUE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TRUNC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . UPPER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . URL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . VAR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . VARP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . WEEKDAY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . XROOT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . YEAR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5
43 44 45 45 46 46 46 47 47 48 48 48 49 50 50 50 51 51 52 52 53 53 54 54 55 55 56 57 57 58
Table of Contents
6
Formula Operators and Functions Spread provides operators and predefined formula functions to use when you create formulas.
Formula Operators The following table lists the available operators. Operator
Description
Literal / Literal
Cell Ref / Literal
Cell Ref / Cell Ref
+
Addition
2+2
A1 + 2
A1 + B2
–
Subtraction
4–2
A1 – 2
A1 – B2
^
Exponentiation
2^2
A1 ^ 2
A1 ^ B2
*
Multiplication
2*2
A1 * 2
A1 * B2
/
Division
2/2
A1 / 2
A1 / B2
&
Logical And
2&2
A1 & 2
A1 & B2
|
Logical Or
2|2
A1 | 2
A1 | B2
:
Creates a range
#
Wild card
A1: B2 A# * 2
A# * B#
Note The wild card operator (#) is available only when the reference style is SS_REFSTYLE_DEFAULT. For more information, see “Specifying Cell References” in Chapter 6, “Working with Data,” in the User’s Guide.
Formula Functions The following sections describe the formula functions available. Each function provides an example. Examples that provide results give decimal values for 10 decimal places. For most functions, the arguments can be number values, cell references, or mathematical expressions that contain cell references, or other values as described in the section.
Data Types for Formula Functions The formula functions receive data through arguments and then return data. The data they accept and return fall under three main data types: •
Strings
•
Numbers
•
Boolean values
Boolean values are handled in Spread as numeric values. Zero is considered False, and any non-zero number is considered True. When a function returns a Boolean value, it returns a zero (False) or a one (True).
7
Data Types for Formula Functions
To determine which cell type you can use to provide data for a function, and which cell type can accept the return value from a function, you need to look at the input data types the function accepts and returns. Then, look at the data types each cell type provides for formula functions. The cell types fall under the data types as follows: Data Type Cell Type
String
Number
✓ (three-state)
Check box
✓ ✓
Float
✓
Integer
✓ ✓
Owner-Drawn Pic (Mask)
✓ ✓
Picture Static Text
✓ (two-state)
✓
Date Edit
Other
✓
Button
Combo box
Boolean
✓ ✓
Time
Note These data types are the ones provided by each cell type for formula functions. The formula functions work with cell data in a different manner than other spreadsheet features. If you try to use a function in a cell type that does not use the data type returned by that function, the function appears not to return a value. For example, if you set the Formula property to use a numeric function, such as ABS( ), in an edit cell, the cell does not display a value. Note that many functions will look for input as a certain data type, such as a number, and also will allow input from an empty cell without causing an error. Therefore, if you have a function operating on a range of cells, such as SUM( ), some of which contain numbers and some of which are empty, the function will calculate properly, using the values in the cells with numbers. Empty cells are cells that have not been assigned a cell type, and that do not contain data. As you can see from the table of data types and cell types, it is possible to return data from a function into a cell type that might not operate as you intend. For example, if you were to set a formula in a check box cell, and the formula returned a non-zero value, the check box cell would represent that value as a selected check box. However, the check box has no way to display the actual value, which is probably not what you intend. Keep this in mind when using formulas.
8
ABS
ABS Name Description Syntax Remarks
Absolute value Returns the absolute value of the specified value. ABS(value) The value argument can be any real number. See also NEG and SIGN.
Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. ABS(R1C2) ABS(B3) ABS(–4)=4
ACOS Name Description
Syntax Remarks
Arccosine Returns the angle whose cosine is the specified value. The angle is in radians between 0 (zero) and π (pi). ACOS(value) The value argument is the cosine of the angle you want to return and must be between –1 and 1. If you want to convert the result to degrees, multiply it by 180/π. See also ACOSH, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, COSH, SIN, SINH, TANH, and TAN.
Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. ACOS(R1C2) ACOS(B3) ACOS(0.5)=1.0471975512
ACOSH Name Description Syntax Remarks
Inverse hyperbolic cosine Returns the inverse hyperbolic cosine of the specified value. ACOSH(value) For the Value argument, you can specify any real number greater than or equal to 1. Note that acosh(cosh(value)) = value. See also ACOS, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, COSH, SIN, SINH, TANH, and TAN.
Data Type
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7.
9
ADD
Example
ACOSH(1)=0 ACOSH(10)=2.9932228461
ADD Name Description Syntax Remarks Data Type
Example
Addition Adds the two arguments. ADD(value1, value2) See also SUM. Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. ADD(R1C2, R2C3) ADD(B3, C4) ADD(-1, 9)=8
AND Name Description
Syntax Remarks
And Returns 1 (True) if all its arguments are true; otherwise, returns 0 (False) if one or more arguments are false. AND(bool1, bool2, …) Accepts up to 30 arguments. Provide numeric or logical values for the arguments. See also FALSE, IF, NOT, OR, and TRUE.
Data Type
Example
Accepts numeric (boolean) data. Returns numeric (boolean) data. For more information, see “Data Types for Formula Functions” on page 7. AND(TRUE(), FALSE()) = 0 (False) AND(5+3=8, 5+1=6) = 1 (True)
ASIN Name Description
Syntax Remarks
Arcsine Returns the angle whose sine is the specified value. The angle is in radians between –π/2 and π/2. ASIN(value) The value argument is the sine of the angle you want to return and must be between –1 and 1. If you want to convert the result to degrees, multiply it by 180/π. See also ACOS, ACOSH, ASINH, ATAN, ATAN2, ATANH, COS, COSH, SIN, SINH, TANH, and TAN.
Data Type
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7.
10
ASINH
Example
ASIN(R1C2) ASIN(B3) ASIN(0.5)=0.5235987756
ASINH Name Description Syntax Remarks
Inverse hyperbolic sine Returns the inverse hyperbolic sine of a number. ASINH(value) The value argument is any real number. Note asinh(sinh(value)) equals value. See also ACOS, ACOSH, ASIN, ATAN, ATAN2, ATANH, COS, COSH, SIN, SINH, TANH, and TAN.
Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. ASINH(R1C2) ASINH(-5.5)=-2.40606 ASINH(100)=5.2983423656
ATAN Name Description
Syntax Remarks
Arctangent Returns the angle whose tangent is the specified value. The angle is in radians between –π/2 and π/2. ATAN(value) The value argument is the tangent of the angle you want to return and must be between –1 and 1. If you want to convert the result to degrees, multiply it by 180/π. See also ACOS, ACOSH, ASIN, ASINH, ATAN2, ATANH, COS, COSH, SIN, SINH, TANH, and TAN.
Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. ATAN(R1C2) ATAN(B3) ATAN(1)=0.7853981634
11
ATAN2
ATAN2 Name Description
Syntax Remarks
Arctangent 2 Returns the arctangent of the specified x- and y-coordinates. The result is given in radians between –π and π, excluding –π. ATAN2(value1, value2) The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (value1, value2). If you want to convert the result to degrees, multiply it by 180/π. See also ACOS, ACOSH, ASIN, ASINH, ATAN, ATANH, COS, COSH, SIN, SINH, TANH, and TAN.
Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. ATAN2(R1C2,R3C5) ATAN2(A1,C3) ATAN2(1,1)=0.7853981634
ATANH Name Description Syntax Remarks
Inverse hyperbolic tangent Returns the inverse hyperbolic tangent of a number. ATANH(value) The value argument is any real number between 1 and –1, excluding –1 and 1. Note that atanh(tanh(value)) equals value. See also ACOS, ACOSH, ASIN, ASINH, ATAN, ATAN2, COS, COSH, SIN, SINH, TANH, and TAN.
Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. ATANH(B5) ATANH(0.55)=0.6183813136 ATANH(-0.2)=-0.2027325541
AVERAGE Name Description Syntax Remarks
Average Returns the average of the specified values. AVERAGE(value1, value2, . . . ) Accepts up to 30 arguments. Each argument can be a cell range, a float value, or an integer value. See also MEDIAN and MODE.
12
CEILING
Data Type
Example
Accepts numeric data for all arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. AVERAGE(A1,B3,D5,E9) AVERAGE(R1C2,R3C1) AVERAGE(A1:A9) AVERAGE(98,72,85)=85
CEILING Name Description Syntax Remarks
Ceiling Rounds a number up to the nearest multiple of a specified value. CEILING(value1, value2) Specify the number to round using the value1 argument. Specify the value to use as the rounding factor using the value2 argument. Use either both positive or both negative numbers for the arguments. Regardless of the sign of number, the number is rounded away from zero. See also EVEN, FLOOR, INT, ODD, ROUND, ROUNDDOWN, ROUNDUP, and TRUNC.
Data Type
Example
Accepts numeric data for both arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. CEILING(R1C2,1) CEILING(C4,B2) CEILING(B3,0.05) CEILING(4.65,2)=6 CEILING(-2.78,-1)=-3
CHAR Name Description Syntax Remarks
Character Returns the character specified by a number. CHAR(value) For the value argument, use a number between 1 and 255 specifying which character you want from the Windows character set (ANSI). See also LEFT, LOWER, MID, PROPER, RIGHT, and UPPER.
Data Type
Example
Accepts numeric data. Returns string data. For more information, see “Data Types for Formula Functions” on page 7. CHAR(B2) CHAR(66)=B CHAR(218)=Ú
13
CLEAN
CLEAN Name Description Syntax Remarks
Clean Removes all nonprintable characters from text. CLEAN(text) The text argument is any data from which you want to remove nonprintable characters. Use the CLEAN function to remove text that contains characters that might not print with your operating system. For example, you can use the CLEAN function to remove some low-level computer code, which is frequently at the beginning and end of data files and cannot be printed. See also TRIM.
Data Type
Example
Accepts string data. Returns string data. For more information, see “Data Types for Formula Functions” on page 7. In this example, Chr(7) returns a nonprintable character. fpSpread1.Formula = "CLEAN(""" + Chr(7) + ➥"text" + Chr(7) + """)" equals “text”
CODE Name Description
Syntax Remarks
Data Type
Example
Code Returns a numeric code to represent the first character in a text string. The returned code corresponds to the Windows character set (ANSI). CODE(text) The text argument is the text from which you want to determine the code of the first character. Accepts string data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. fpSpread1.Formula = "CODE(""B"")" equals 66 fpSpread1.Formula = "CODE(""Buffalo"")" equals 66
COMBIN Name Description Syntax Remarks
Combination Returns the number of possible combinations for a specified number of items. COMBIN(value1, value2) Specify the number of items using the value1 argument. Specify the number of items in each possible combination using the value2 argument. Both arguments must be positive integers, and the value1 argument must be greater than or equal to the value2 argument. A combination is any set or subset of items, regardless of the internal order of the items. Contrast with permutation (the PERMUT function).
14
CONCATENATE
The number of combinations is calculated as follows, where value1=n and value2=k:
n! k, n n = P --------- = --------------------- k k! k! ( n – k )! where:
n! P k, n = ----------------( n – k )! See also PERMUT. Data Type
Example
Accepts numeric data for both arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. combin(R1C2,2) combin(C4,B2) combin(B3,5) COMBIN(8,2)=28 COMBIN(100,3)=161700
CONCATENATE Name Description Syntax Remarks
Concatenate Combines multiple text strings or numbers into one text string. CONCATENATE(text1, text2, …) Accepts up to 30 arguments. Arguments can be strings, formulas that return a string, or references to cells containing a string.
Data Type
Accepts string data for all arguments. Returns string data. For more information, see “Data Types for Formula Functions” on page 7.
Example
fpSpread1.Formula = "CONCATENATE(""Gold "", ""Medal"")" equals “Gold Medal” In the following example, cell A1 contains "Baker", cell A2 contains " runs", and cell A3 contains " the 5-mile race.". fpSpread1.Formula = "CONCATENATE(A1,A2,A3)" equals “Baker runs the 5mile race.”
COS Name Description Syntax Remarks
Cosine Returns the cosine of the specified angle. COS(value) Specify the angle in radians using the value argument. If the angle is in degrees, multiply it by π/180 to convert it to radians.
15
COSH
See also ACOS, ACOSH, ASIN, ASINH, ATAN, ATAN2, ATANH, COSH, SIN, SINH, TANH, and TAN. Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. COS(R1C3) COS(B2) COS(45*pi()/180)=0.7071067812
COSH Name Description Syntax Remarks
Hyperbolic cosine Returns the hyperbolic cosine of the specified value. COSH(value) You can specify any real number for the value argument. The hyperbolic cosine is calculated as follows: z
–z
+e cosh ( z ) = e----------------2 See also ACOS, ACOSH, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, SIN, SINH, TANH, and TAN. Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. COSH(R1C2) COSH(B3) COSH(4)=27.3082328360
DATE Name Description Syntax Remarks
Date Returns the serial value for a particular date. DATE(Year, Month, Day) The arguments are as follows: Argument
Description
Year
A four-digit number from 1900 to 9999.
Month
A number representing the month of the year. If Month is greater than 12, then Month increments by the number of months over 12 and the year advances, if needed. For example, DATE(2000,16,2) returns the serial value representing April 2, 2001.
16
DAY
Argument
Description
Day
A number representing the day of the month. If Day is greater than the number of days in the specified month, then Day increments that number of days from the first day of the next month. For example, DATE(2000,1,35) returns the serial value representing February 4, 2000.
Spread’s calc engine processes dates as a numeric value in the form x.y, where x is the “number of days since December 30, 1899” and y is the fraction of day. Numbers to the left represent the date, which is the value that the DATE function returns. Use the TIME function to return the serial value for the time value (the fraction of a day). Date values are based on the number of days elapsed since December 30, 1899, which has a date serial value of Day 0. For example, December 31, 1899 has a date serial value of 1. Date values prior to December 30, 1899 have negative date serial values. For example, the date value December 29, 1899 has a date serial value of –1. See also DAY, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TODAY, WEEKDAY, and YEAR. Data Type
Example
Accepts numeric data for all arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. DATE(A1,B1,C1) DATE(2000,1,1)=36526 DATE(2001,2,10)=36932
DAY Name Description
Syntax Remarks
Day Returns the day number of the month (integer 1 to 31) that corresponds to the specified date. DAY(date) Specify the date argument as a serial value or date value. To provide a serial value from a date value, use the DATE or TODAY function. Spread’s calc engine processes dates as a numeric value in the form x.y, where x is the “number of days since December 30, 1899” and y is the fraction of day. Numbers to the left represent the date, which is the value that the DATE function returns. Use the TIME function to return the serial value for the time value (the fraction of a day). Date values are based on the number of days elapsed since December 30, 1899, which has a date serial value of Day 0. For example, December 31, 1899 has a date serial value of 1. Date values prior to December 30, 1899 have negative date serial values. For example, the date value December 29, 1899 has a date serial value of –1. See also DATE, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TODAY, WEEKDAY, and YEAR.
Data Type
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7.
17
DB
Example
DAY(A2) DAY(33239)=1 (33239 is the serial value for January 1, 1991)
DB Name Description
Syntax Remarks
Depreciation (fixed-declining balance method) Calculates the depreciation of an asset for a specified period using the fixed-declining balance method. DB(Cost, Salvage, Life, Period, Month) Arguments are as follows: Argument
Description
Cost
Initial cost of the asset
Salvage
Value at the end of the depreciation period
Life
Number of periods over which the asset is being depreciated
Period
Period for which you want to calculate the depreciation Use the same units as the Life argument.
Month
(Optional) Number of months in the first year If omitted, the calculation assumes 12 months.
The fixed-declining balance method computes depreciation at a fixed rate. The DB function uses the following formulas to calculate depreciation for a period: (Cost – total depreciation from prior periods) × rate where: rate=1 – ((Salvage/Cost)^(1/Life)), rounded to three decimal places Depreciation for the first and last periods is a special case. For the first period, the DB function uses this formula: Cost × rate × month/12 For the last period, the DB function uses this formula: ((Cost – total depreciation from prior periods) × rate × (12 – month))/12 See also DDB, SLN, and SYD. Data Type
Example
Accepts numeric data for all arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. DB(R1C2,10000,10,1) DB(500000,5000,5,1,10)=250833.3333333333
DDB Name Description
Depreciation (double-declining balance method) Calculates the depreciation of an asset for a specified period using the double-declining balance method or another method you specify.
18
DEGREES
Syntax Remarks
DDB(Cost, Salvage, Life, Period, Factor) Arguments are as follows: Argument
Description
Cost
Initial cost of the asset
Salvage
Value at the end of depreciation
Life
Number of periods over which the asset is being depreciated
Period
Period for which you want to calculate the depreciation Use the same units as the Life argument.
Factor
(Optional) Rate at which the value declines If omitted, the calculation assumes 2 (double-declining method) Change this to a value other than 2 to use a different method.
All five arguments must be positive numbers. The DDB function uses the following formula to calculate depreciation for a period: Cost – Salvage(total depreciation from prior periods) × Factor/Life See also DB, SLN, and SYD. Data Type
Example
Accepts numeric data for all arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. DDB(R1C2,10000,10,1) DDB(500000,5000,5,1,4)=400000
DEGREES Name Description Syntax Remarks Data Type
Example
Degrees Converts the specified value from radians to degrees. DEGREES(value) See also RADIANS. Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. DEGREES(R1C2) DEGREES(B3) DEGREES(pi())=180
EVEN Name Description Syntax Remarks
Even Rounds the specified value up to the nearest even integer. EVEN(value) Regardless of the sign of the number specified by the value argument, the number is rounded away from zero.
19
EXACT
See also CEILING, FLOOR, INT, ODD, ROUND, ROUNDDOWN, ROUNDUP, and TRUNC. Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. EVEN(R1C2) EVEN(A3) EVEN(5)=6 EVEN(-2.5)=-4
EXACT Name Description Syntax Remarks
Exact Returns 1 (True) if two strings are the same; otherwise, 0 (False). EXACT(text1, text2) Compares the first string, the text1 argument, to the second string, text2. Although the EXACT function is case-sensitive, it ignores formatting differences.
Data Type
Example
Accepts string data for both arguments. Returns numeric (boolean) data. For more information, see “Data Types for Formula Functions” on page 7. EXACT(A3,A5) fpSpread1.Formula = "EXACT(""SPREAD"",""spread"")" equals 0 (False)
EXP Name Description Syntax Remarks Data Type
Example
Exponent base e Returns e raised to the power of the specified value (ex). EXP is the inverse of LN. EXP(value) See also LN and POWER. Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. EXP(R1C2) EXP(B3) EXP(1)=2.7182818285 EXP(ln(4))=4
FACT Name Description Syntax Remarks
Factorial Returns the factorial of the specified number. FACT(value) The value argument must be a nonnegative number. The factorial is calculated as 1×2×3×
. . . ×value. If you provide a number that is not an integer for the value argument, the decimal portion of the number is ignored.
20
FALSE
See also PRODUCT. Data Type
Example
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. FACT(R1C2) FACT(B3) FACT(10)=3628800
FALSE Name Description Syntax Remarks
False Returns the value 0 (False). FALSE( ) The function does not accept arguments. See also AND, IF, NOT, OR, and TRUE.
Data Type
Example
Does not accept data. Returns numeric (boolean) data. For more information, see “Data Types for Formula Functions” on page 7. FALSE()
FIND Name Description
Syntax Remarks
Find Finds one text value within another. Returns the text value’s position in the text you searched. FIND(Find_text, Within_text, Start_num) Arguments are as follows: Argument
Description
Find_text
Text you are trying to find. If Find_text is “ ” (empty text), the FIND function matches the first character in the search string (that is, the character numbered Start_num or 1). Find_text cannot contain wildcard characters.
Within_text
Text through which you are searching.
Start_num
(Optional) The character at which to start the search. The first character in Within_text is character number 1. If you omit Start_num, it is assumed to be 1.
The FIND function performs a case-specific search (for example, to specify a capital letter and not lower case letters). See also REPLACE and SUBSTITUTE. Data Type
Accepts string data for the Find_text argument, string data for the Within_text argument, and numeric data for the Start_num argument. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7.
21
FLOOR
Example
fpSpread1.Formula = "FIND(""P"",""FarPoint ➥Technologies"")" equals 4 fpSpread1.Formula = "FIND(""n"",""FarPoint ➥Technologies"",8)" equals 14 fpSpread1.Formula = "FIND(""G"",A2,1)"
FLOOR Name Description Syntax Remarks
Floor Rounds a number down to the nearest multiple of a specified value. FLOOR(value1, value2) Specify the number to round using the value1 argument. Specify the value to use as the rounding factor using the value2 argument. Use either both positive or both negative numbers for the arguments. Regardless of the sign of number, the number is rounded toward zero. See also CEILING, EVEN, INT, ODD, ROUND, ROUNDDOWN, ROUNDUP, and TRUNC.
Data Type
Example
Accepts numeric data for both arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. FLOOR(R1C2,1) FLOOR(C4,B2) FLOOR(B3,0.05) FLOOR(4.65,2)=4 FLOOR(-2.78,-1)=-2
FV Name Description
Syntax Remarks
Future Value Returns the future value of an investment based on a present value, periodic payments, and a specified interest rate. FV(Rate, Nper, Pmt, Pv, Type) Arguments are as follows: Argument
Description
Rate
Interest rate expressed as percentage (per period)
Nper
Total number of payment periods
Pmt
Payment made each period
Pv
(Optional) Present value If omitted, it is assumed to be zero and the calculation is based on the Pmt argument.
22
HOUR
Argument
Description
Type
(Optional) Indicates when payments are due If omitted, it is assumed that the payments are made at the end of the period. Set Type equal to
If payments are due
0
At the end of the period
1
At the beginning of the period
Note You must specify a value for the Pv argument if you are going to specify a value for the Type argument. Use consistent units for specifying the Rate and Nper arguments. If you make monthly payments on a five-year loan at 8 percent annual interest, use 0.08/12 for the Rate argument and 5*12 for the Nper argument. If you make annual payments on the same loan, use 0.08 for Rate and 5 for Nper. For all the arguments, money paid out such as deposits in an investment, is represented by negative numbers; money you receive, such as dividend checks, is represented by positive numbers. See the PV function for the formula used to calculate financial values. See also NPER, PMT, PV. Data Type
Example
Accepts numeric data for all arguments. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7. FV(R1C1/12,48,R1C2,1000,0) FV(0.005,60,-100,100,1)=6877
HOUR Name Description Syntax Remarks
Hour Returns the hour that corresponds to the provided time. HOUR(time) Specify the time argument as a serial value or time value. To provide a serial value from a time value, use the NOW or TIME function. The returned hour is an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). Spread’s calc engine processes dates as a numeric value in the form x.y, where x is the “number of days since December 30, 1899” and y is the fraction of day. Numbers to the left represent the date, which is the value that the DATE function returns. Use the TIME function to return the serial value for the time value (the fraction of a day). Time serial values are represented as decimal values ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.). See also DATE, DAY, MINUTE, MONTH, NOW, SECOND, TIME, TODAY, WEEKDAY, and YEAR.
Data Type
Accepts numeric data. Returns numeric data. For more information, see “Data Types for Formula Functions” on page 7.
23
IF
Example
HOUR(A2) HOUR(0.55) = 13 HOUR(347.55) = 13
IF Name Description Syntax Remarks
Comparison Returns a value based on a logical value. IF(value1, value2, value3) value1 must be or evaluate to numeric data, where nonzero values indicate True, and a value of zero indicates False. If value1 is nonzero (or True), then value2 is returned. If value1 is zero (or False), then value3 is returned. value1 can contain one of the relational operators: greater than (>), less than (65,1000,2000) IF(A32000,1900,2000) IF(C4,B2,B4) IF(1>2,5,10)=10 fpSpread1.Formula = "IF(1
