Functions
*********

Cell functions take a comma-separated list of arguments in parentheses, and return a value based on those values.  In addition to the standard value types, some arguments may be cell ranges.

Boolean Functions
=================

Boolean functions return either a #TRUE or #FALSE value. Most arguments (those labelled BOOL) are also boolean.  If these arguments have any other value instead, the function returns #NON_BOOL as an error.

if(BOOL, VAL1, VAL2)
     If BOOL is #TRUE, then VAL1 is returned.  If BOOL is #FALSE, VAL2 is returned.

and(BOOL1, BOOL2)
     Returns #TRUE if and only if both arguments are #TRUE, otherwise returns #FALSE.

or(BOOL1, BOOL2)
     Returns #TRUE if either or both arguments are #TRUE, otherwise returns #FALSE.

not(BOOL)
     Returns #TRUE if BOOL is #FALSE; #FALSE if #TRUE.

iserr(VAL)
     returns #TRUE if VAL is an error, #FALSE otherwise.

isnum(VAL)
     #TRUE if VAL is a number, or can be automatically converted to a number.  Thus, isnum("12") is #TRUE, while isnum("foobar") is #FALSE.

Elementary mathematical functions
=================================

General Math Functions
----------------------

For these these functions, the argument X can be a number (integer or floating point), a reference to a cell containing an integer, or a reference to a cell containing a quoted integer.  Thus, if cell (how do I make a reference to a cell?) A2 contains "-12.34", then all of the following expressions compute the absolute value of -12.34:

                    @abs(-12.34)
                    @abs(A2)
                    @abs(@cell(2,1,"value"))

abs(X)
     Returns the absolute value of X.

negate(X)
     Returns  -X.

int(X)
     int() converts X to an integer by truncating the fractional part.  For instance,

                     int(2.3)     =>   2
                     int(-2.3)    =>  -2

ceil(X)
     ceil() replaces X by the least integer greater than or equal to X.

                     ceil(2.3)     =>   3
                     ceil(-2.3)    =>  -2

floor(X)
     floor() replaces X by the largest integer less than or equal to X.

                     floor(2.3)     =>   2
                     floor(-2.3)    =>  -3

log(X)
     The natural log  of X.  If X is negative, the result is #MINUS_INFINITY, and the cell displays is #OUT_OF_RANGE.

exp(X)
     The  exponential of X

log10(X)
     The log base 10 of X.  If X is negative, the result is #MINUS_INFINITY, and the cell displays is #OUT_OF_RANGE.

sqrt(X)
     The square root of X.  If X is negative, the error message is #OUT_OF_RANGE.

rnd(X)
     Generates a random integer from 0 to X-1.  This is updated regularly. This can get annoying. To change the time that it is updated from 1 second to 10 seconds, type

                  M-x set-option  RET ticks 10  RET

fixed(X, PLACES)
     Rounds X to PLACES decimal places.

Trigonometric Functions
-----------------------

pi()
     Constant; 15 decimals of pi, 3.141592653589793.

sin(ANG)
     The sine of angle ANG (in radians).

cos(ANG)
     The  cosine of angle ANG (in radians).

tan(ANG)
     The  tangent of angle ANG (in radians).

asin(VAL)
     The arc sine (in radians) of VAL.

acos(VAL)
     The arc  cosine (in radians) of VAL.

atan(VAL)
     The arc  tangent (in radians) of VAL.

dtr(DEG)
     dtr converts degrees to radians.

rtd(RAD)
     rtd converts radians  to degrees.

atan2(Y, X)
     Expanded arc tangent; returns the arc tangent (in radians) of Y / X.  The range of atan2 is (-pi..pi). The quadrant of the angle returned is determined by the quadrant of the point (X, Y).

hypot(X, Y)
     Returns the length of the hypotenuse of a triangle with sides X and Y; i.e., sqrt(X*X + Y*Y). (see General math functions)

Statistical Functions
---------------------

These functions take a variable number of arguments, labeled VR1, VR2, etc.  Each of these values can be a numeric type or a range.  If the value is a range, the values of all numeric cells in that range are used (strings, empty cells, etc. are ignored).  Values can also be strings that are easily convertible to numbers; i.e., "123".

If none of the arguments include valid numbers, then the value of the function is #NO_VALUES.  To ensure that the expression doesn't return an error in such cases, you can provide a default value as an extra argument (e.g., @sum(0,r1:10c1:10)).

The spreadsheet below is used for examples of the statistical functions.

                  | Col B | Col C | Col D |
            ------|-------|-------|-------|
            Row 2 |   2   |   3   |   5   |
            ------|-------|-------|-------|
            Row 3 |   8   |   4   |   6   |
            ------|-------|-------|-------|
            Row 4 |   9   |   1   |   3   |
            ------|-------|-------|-------|
            Row 5 |   4   |   7   |   0   |
            ------|-------|-------|-------|

sum(VR1, ...)
     The sum  of the given values.  Each value can be a numeric value or a range.

     18
          @sum(B2.B5)

     20
          @sum(B2.B5,2)

     25
          @sum(B2.B5,C4.C5)

prod(VR1, ...)
     The product  of the given values.  Each value can be a numeric value or a range.

     512
          @prod(B2.B5)

     1024
          @prod(B2.B5,2)

     0
          @prod(B2.B5,C4.C5)

count(VR1, ...)
     The number of numeric values found among the arguments. In the example, assume that the first row and column are not numbers.

     4
          @count(B2.B5)

     4
          @count(B2.B5,A1.D1)

     6
          @count(B2.B5,C4.C5)

max(VR1, ...)
     The maximum  value of all the arguments.  Non-numeric values are ignored.

     8
          @max(B2.B5)

     8
          @max(B2.B5,A1.D1)

     8
          @max(B2.B5,C4.C5)

min(VR1, ...)
     The  minimum value of all the arguments.

     2
          @min(B2.B5)

     2
          @min(B2.B5,A1.D1)

     1
          @min(B2.B5,C4.C5)

avg(VR1, ...)
     The average of the given values.  The sum of the numerical values in the arguments is divided by the number of numeric values, not by the number of cells.

     4.5
          @avg(B2.B5)

     4.5
          @avg(B2.B5,A1.D1)

     4.333
          @avg(B2.B5,C4.C5)

std(VR1, ...)
     The sample standard deviation.  To get the population standard deviation, use @sqrt(@var(...)).

     2.51
          @std(B2.B5)

     2.51
          @std(B2.B5,A1.D1)

     2.73
          @std(B2.B5,C4.C5)

var(VR1, ...)
     The population variance of the arguments.  To get the sample variance, use @std(...)^2.

     4.75
          @var(B2.B5)

     4.75
          @var(B2.B5,A1.D1)

     6.22
          @var(B2.B5,C4.C5)

String Functions
================

Strings indexes start with 1 for the first character.  Indices less than 1 or greater than the length of the corresponding string will generate an #OUT_OF_RANGE error return, unless otherwise noted.

These functions are in the `string' package.  If Oleo is compiled with -DUSE_DLD, the string package must be loaded before these functions can be used.  If a spreadsheet that uses these functions is loaded before the string package is loaded, things will fail (silently!).  This is a bug.

len(STR)
     Returns the number of characters in STR.

strupr(STR)
     Converts STR to all upper  case.

strlwr(STR)
     Converts STR to all  lower case.

strcap(STR)
     Capitalizes each word in STR.

trim(TRIM)
     String beautifier.  Trims extra spaces and non-ASCII characters from STR.

find(STR1, STR2, POS)
     Returns the index at or beyond POS where STR2 appears in STR1  (Actually, due to a bug in version 1.5 STR1 is searched for in STR2.).  If STR2 does not appear at or beyond POS, 0 is returned.

substr(POS1, POS2, STR)
     Extracts the substring from index POS1 through POS2 of STR.  Positive indices, starting with 1, count from the start of the string, and negative indices (from -1) count from the end of the string.  POS1 must occur at or before POS2.

mid(STR, POS, LEN)
     Extracts the substring of LEN characters starting at POS in STR.  The substring is truncated if it begins or extends beyond the end of STR (In version 1.5 this function counts indices from 0 instead of 1 as with other string functions; this is probably a bug.).

edit(STR, POS1, POS2)
     Deletes the substring from POS1 through POS2 of STR. Negative indices can be used as with substr.

repeat(STR, NUM)
     Returns a string of NUM concatenations of STR with itself; e.g., repeat("foo", 2) returns "foofoo".

concat(VR1, ...)
     Returns the concatenation of all its arguments.  An argument may be a range, in which case the cell values in the range are concatenated.

Structural Functions
====================

These functions return information about a cell or range of cells. Some of them are in the `cells' package; if Oleo is compiled with -DUSE_DLD, the cells package must be loaded first.

row()
col()
     The row or column of the cell the expression is in.

rows(RNG)
cols(RNG)
     The number of rows or columns in RNG.

my(KEY)
     Returns information about the cell containing the expression according to the key string KEY.  Valid values for KEY are:

     "row"
          The cell's row address (an integer).

     "column"
          The cell's column address.

     "width"
          Width of the cell.

     "lock"
     "protection"
          "locked" or "unlocked".

     "justify"
     "alignment"
          "left", "right", "center" or "default".

     "format"
     "fmt"
          "default", "user-1", etc.

     "type"
          The type of the value of the cell: "error", "boolean", "float", "integer", "null", "unknown", etc.

     "formula"
          A string of the cell's current formula.

     "value"
          The cell's current value.

     Any other value generates #BAD_INPUT.

curcell(KEY)
     Same as my(), but returns information about the cell where the cell-cursor is.

cell(ROW, COL, KEY)
     Same as my(), but for the cell at (ROW, COL).  Be careful about the indices in a0 mode.  The cell in row 2, column 1 is A2, not B1.

Search Functions
================

These functions search for a value or string in a range of cells, and return the index of the first cell that matches.  Cells are numbered from 1 in column-major order; e.g., in the range r1:2c1:3 cell r1c1 is index 1, cell r2c1 is 2, cell r1c2 is 3, etc.

member(RNG, VAL)
     Returns the index of the first cell in RNG that contains value VAL, or 0 if no cells contain it.  VAL may be an error value.

smember(RNG, STR)
     Search for a substring.  Returns the index of the first cell in RNG that is a substring of STR, or 0 if no cells are contained in STR.  If STR is empty, it matches empty cells as well  (An empty cell is a cell with no value; a cell with an empty string value ("") would match anyway, since an empty string is a substring of an empty string.).

members(RNG, STR)
     Like members(), but vice-versa: matches the first cell with a substring of STR (i.e., STR is contained in the cell).  If STR is empty, empty cells are still ignored (unlike members()).

pmember(RNG, STR)
memberp(RNG, STR)
     Same as smember() and members(), respectively, but these search for an initial string STR of the cells in RNG.

index(RNG, INDEX)
index(RNG, ROWOFFSET, COLOFFSET)
     The first form returns the contents of the cell at index INDEX in RNG.  The second form returns the cell in RNG that is ROWOFFSET rows and COLOFFSET columns from the upper left.

oneof(CHOICE, VAL1, ...)
     Case expression.  Returns VAL1 if CHOICE is 1, the next value if 2, etc.  If CHOICE is not a valid integer in the appropriate range, #OUT_OF_RANGE is returned.  If no value arguments are supplied, #NO_VALUES is returned.  Note that arguments must be values; ranges are not allowed.

These functions are used to index into a table of entries:

hlookup(RNG, NUM, ROWOFFSET)
     Scans the top row of RNG looking for a number which is greater than NUM, then returns the value in the cell that is ROWOFFSET rows down from the top of the range.

vlookup(RNG, NUM, COLOFFSET)
     Like vlookup(), but vertically: scans the first column of RNG for a value greater than NUM, and returns the cell value COLOFFSET columns to the right.

vlookup_str(RNG, STR, COLOFFSET)
     Like vlookup(), but looks for string STR.

Business Functions
==================

Functions for various business-related calculations.  Rates are expressed as exactly, i.e. don't expect 10 to mean 10%, but rather use 0.10.  The units for TERM are payment periods.  Thus, if you want to calculate any of the following using, for example, monthly componunding, you will want pmt(p, r/12, t*12), where T is in years.

pmt(P, R, TERM)
     Payment per period for a loan of principal P at rate R for TERM payments, assuming a fully amortizing loan which compounds at the payment rate.

pv(PMT, R, TERM)
     Present value of an investment that pays PMT at the end of each of TERM periods with a discount (interest) rate of R.

npv(RNG, R)
     Net present value of an investment which will pay uneven payments.  The term is calculated from the number of cells in range RNG, with the first cell corresponding to T=0 (i.e., assuming a beginning of year paymest stream).  Entries in the range can be positive or negative, corresponding to receipts or payments, respectively.

irr(RNG, GUESS)
     Internal rate of return.  Given a range of cells corresponding to uneven periodic payments, calculate the rate R such that npv(rng,r)==0.  This function can be multivalued when entries in the range change sign more than once, and hence returns #BAD_INPUT if it fails to converge.  Argument GUESS helps the function return more quickly, but is not critical.

fmrr(RNG, SAFE, REINVEST, REINVMIN)
     Financial management rate of return.  This function attempts to be a more encompassing and consistent version of the internal rate of return, which ignores the disposition of intermediate cash in- and outflows.  The fmrr function pulls back negative values to the nearest positive ones at the SAFE rate (e.g. certificate of deposit rate), and pushes forward all outflows to the end of the period as specified by RNG. These outflows generate income at the REINVESTment rate if they accumulate to be over REINVINMIN, otherwise at the SAFE rate.

fv(PMT, R, TERM)
     Future value of an annuity that pays PMT at the end of each of TERM periods assuming a discount rate of R.

rate(FUT, PRES, TERM)
     Interest rate required to turn current principal PRES into future principal FUT in TERM periods.

term(PMT, R, FUT)
     Number of periods required to collect FUT in payments of PMT, with an interest rate of R.

cterm(R, FUT, PRES)
     Number of periods required to collect FUT from a single deposit of PRES, at an interest rate of R.

sln(COST, SCRAP, LIFE)
     Straight line depreciation of an asset that cost COST when new, can be sold for SCRAP, and will last for LIFE periods.

syd(COST, SCRAP, LIFE, PER)
     Sum-of-the-digits depreciation in period PER of an asset that cost COST, can be sold for SCRAP, and lasts LIFE periods.

ddb(COST, SCRAP, LIFE, PER)
     Double-declining-balance depreciation; similar to syd().

anrate(PMT, P, TERM)
     Solve pmt(pmt, r, term) == p for R.

anterm(PMT, PRIN, R)
     Solve pmt(pmt, r, term) == p for TERM.

balance(P, R, TERM, N)
     After N payments (of pmt(p, r, term)) towards a debt of P at interest rate R over TERM periods, this function gives the amount left due.

paidint(P, R, TERM, N)
     After N payments (of pmt(p, r, term)) towards a debt of P at interest rate R over TERM periods, this function gives the sum of the amounts of the payments which have gone to service the interest on the debt.

kint(P, R, TERM, N)
     At period N, this amount goes towards paying the interest on the amortizing loan at rate R, term TERM, and principal P.

kprin(P, R, TERM, N)
     At period N, this amount goes towards reducing the principal on the amortizing loan at rate R, term TERM, and principal P. Note that kint(p,r,term,n)+kprin(p,r,term,n)==pmt(p,r,term).

compbal(P, R, TERM)
     Compound balance.  Returns the value of principal P after earning interest R over TERM terms.

Date Functions
==============

Functions for manipulating date and time.  Arguments named TIME are times in seconds, either an arbitrary count or from a specific epoch (i.e., local time), unless otherwise noted.  Here are some examples. Assume that the date is December 16, 1994, and the time is 7:56 am. The left hand column is the command, and the right hand column is the result.

               local_month(now())             11
               ctime(now())                   Fri Dec 16 7:58:34 1994
               strftime("%d-%b-%C",now())     11-12-94

now()
     Time of day.  Returns the current time in seconds since Jan 1 1970.

ctime(TIME)
     Converts TIME into a date/time string.  TIME is seconds since the the epoch, as returned by now().

hms_to_time(HOURS, MINUTES, SECONDS)

dhms_to_time(DAYS, HOURS, MINUTES, SECONDS)
     Converts DAYS, HOURS, MINS, SECONDS to a count of seconds.

time_to_d(TIME)

time_to_h(TIME)

time_to_m(TIME)

time_to_s(TIME)
     Returns the number of days, hours (0..23), minutes (0..59), or seconds (0..59) in TIME seconds.

ymd(YEAR, MONTH, DAY)

ymd_dst(YEAR, MONTH, DAY, DST)
     Convert YEAR, MONTH, and DAY to the number of seconds since January 1, 1970 (typically).  The conversion assumes the local time zone.  For ymd_dst(), if DST is positive, daylight savings time is assumed; if 0, standard time; and if negative, neither (i.e., the same as ymd()).

local_year(TIME)

local_month(TIME)
     For a time in seconds (e.g., as returned by now()) return its year or month in the local timezone.

local_date(TIME)
     Returns the date in the local timezone of TIME.

local_hour(TIME)

local_min(TIME)

local_sec(TIME)
     Returns the hour, minutes or seconds in the local timezone of TIME.

local_isdst(TIME)
     Returns DST in the local timezone for TIME.  The return is positive if daylight savings time is in effect, 0 otherwise.

local_yday(TIME)

local_wday(TIME)
     Returns the give day-of-year (0..364 or 0..365 for leap years) or day-of-week (0..6) in the local timezone for TIME.

gmt_year(TIME)

gmt_month(TIME)

gmt_date(TIME)
     Returns the year, month, or day in Greenwich of TIME.

gmt_hour(TIME)

gmt_min(TIME)

gmt_sec(TIME)
     Returns the hour, minutes or seconds in Greenwich of TIME.

gmt_isdst(TIME)
     Returns a positive value if daylight savings time is in effect in Greenwich TIME, 0 otherwise.

gmt_yday(TIME)

gmt_wday(TIME)
     Returns the day-of-year (0..365) or day-of-week (0..6) in Greenwich.

get_date(DATE)

posix_date(DATE)
     Parse a string date and return its time (seconds since epoch).  The syntax of DATE is the same as is understood by GNU tar or GNU date for get_date(), and as per the Posix standard for posix_date().

strftime(FORMAT, TIME)
     Returns a string according to FORMAT describing TIME (as returned by now()).  This function is implemented by the C library function strftime().  The following documentation describes the GNU implementation of strftime().  Your version of Oleo may have been built with a different version, in which case, you can find documentation in the strftime() man page.  (In the future this anomaly will be removed).

     Performs % substitutions similar to those in printf(). Except where noted, substituted fields have a fixed size; numeric fields are padded if necessary.  Padding is with zeros by default; for fields that display a single number, padding can be changed or inhibited by following the % with one of the modifiers described below. Unknown field specifiers are copied as normal characters.  All other characters are copied to the output without change.

     Supports a superset of the ANSI C field specifiers.

     Literal character fields:

     %
          %

     n
          newline

     t
          tab

     Numeric modifiers (a nonstandard extension):

     -
          do not pad the field

     _
          pad the field with spaces

     Time fields:

     %H
          hour (00..23)

     %I
          hour (01..12)

     %k
          hour ( 0..23)

     %l
          hour ( 1..12)

     %M
          minute (00..59)

     %p
          locale's AM or PM

     %r
          time, 12-hour (HH:MM:SS A/PM)

     %R
          time, 24-hour (HH:MM)

     %S
          second (00..61)

     %T
          time, 24-hour (HH:MM:SS)

     %X
          locale's time representation (%H:%M:%S)

     %Z
          time zone (EDT), or nothing if no time zone is determinable

     Date fields:

     %a
          locale's abbreviated weekday name (Sun..Sat)

     %A
          locale's full weekday name, variable length (Sunday..Saturday)

     %b
          locale's abbreviated month name (Jan..Dec)

     %B
          locale's full month name, variable length (January..December)

     %c
          locale's date and time (Sat Nov 04 12:02:33 EST 1989)

     %C
          century (00..99)

     %d
          day of month (01..31)

     %e
          day of month ( 1..31)

     %D
          date (MM/DD/YY)

     %h
          same as %b

     %j
          day of year (001..366)

     %m
          month (01..12)

     %U
          week number of year with Sunday as first day of week (00..53)

     %w
          day of week (0..6)

     %W
          week number of year with Monday as first day of week (00..53)

     %x
          locale's date representation (MM/DD/YY)

     %y
          last two digits of year (00..99)

     %Y
          year (1970...)

GSL Functions
=============

The GNU Scientific Library (GSL) is a collection of routines for numerical computing.  The routines are written from scratch by the GSL team in C, and are meant to present a modern Applications Programming Interface (API) for C programmers, while allowing wrappers to be written for very high level languages.

More information about GSL is available from http://sources.redhat.com/gsl/

Currently only a small subset of the GSL functions (as they are in GSL 0.6) are made available in Oleo. They're most of the statistical functions :

gsl_stats_mean

gsl_stats_variance

gsl_stats_sd

gsl_stats_absdev

gsl_stats_skew

gsl_stats_kurtosis

gsl_stats_lag1_autocorrelation

gsl_stats_median_from_sorted_data

gsl_stats_quantile_from_sorted_data

gsl_stats_variance_m

gsl_stats_sd_m

gsl_stats_variance_with_fixed_mean

gsl_stats_sd_with_fixed_mean

gsl_stats_absdev_m

gsl_stats_skew_m_sd

gsl_stats_kurtosis_m_sd

The API made available in Oleo for these functions differs slightly from the native C API of GSL. The C API provides for a stride and a number parameter when passing a data array. In Oleo, both of these are not explicitly passed.

For example, the gsl_stats_mean function has just one parameter in Oleo, which is a range of cells from which it calculates the mean. oleo_gsl_stats_kurtosis_m_sd has three parameters : a range of cells, and two numbers.