/ 
Spreadsheet-Engine-0.07
River stage one • 1 direct dependent • 1 total dependent
/ Spreadsheet::Engine::Functions

NAME

Spreadsheet::Engine::Functions - Spreadsheet functions (SUM, MAX, etc)

SYNOPSIS

my $ok = calculate_function($fname, \@operand, \$errortext, \%typelookup, \%sheetdata);

DESCRIPTION

This provides all the spreadsheet functions (SUM, MAX, IRR, ISNULL, etc).

EXTENDING

register

Spreadsheet::Engine->register(SUM => 'Spreadsheet::Engine::Function::SUM');

If you wish to make a new function available you should register it here. A series of base classes are provided that do all the argument checking etc., allowing you to concentrate on the calculations. Have a look at how the existing functions are implemented for details (it should hopefully be mostly self-explanatory!)

information on how many arguments should be passed:

EXPORTS

calculate_function

my $ok = calculate_function($fname, \@operand, \$errortext, \%typelookup, \%sheetdata);

FUNCTION providers

series_functions

AVERAGE(v1,c1:c2,...) - See <Spreadsheet::Engine::Function::AVERAGE>
COUNT(v1,c1:c2,...) - See <Spreadsheet::Engine::Function::COUNT>
COUNTA(v1,c1:c2,...) - See <Spreadsheet::Engine::Function::COUNTA>
COUNTBLANK(v1,c1:c2,...) - See <Spreadsheet::Engine::Function::COUNTBLANK>
MAX(v1,c1:c2,...) - See Spreadsheet::Engine::Function::MAX
MIN(v1,c1:c2,...) - See Spreadsheet::Engine::Function::MIN
PRODUCT(v1,c1:c2,...) - See Spreadsheet::Engine::Function::PRODUCT
STDEV(v1,c1:c2,...) - See Spreadsheet::Engine::Function::STDEV
STDEVP(v1,c1:c2,...) - See Spreadsheet::Engine::Function::STDEVP
SUM(v1,c1:c2,...) - See Spreadsheet::Engine::Function::SUM
VAR(v1,c1:c2,...) - See Spreadsheet::Engine::Function::VAR
VARP(v1,c1:c2,...) - See Spreadsheet::Engine::Function::VARP

dseries_functions

DAVERAGE(databaserange, fieldname, criteriarange)
DCOUNT(databaserange, fieldname, criteriarange)
DCOUNTA(databaserange, fieldname, criteriarange)
DGET(databaserange, fieldname, criteriarange)
DMAX(databaserange, fieldname, criteriarange)
DMIN(databaserange, fieldname, criteriarange)
DPRODUCT(databaserange, fieldname, criteriarange)
DSTDEV(databaserange, fieldname, criteriarange)
DSTDEVP(databaserange, fieldname, criteriarange)
DSUM(databaserange, fieldname, criteriarange)
DVAR(databaserange, fieldname, criteriarange)
DVARP(databaserange, fieldname, criteriarange)

lookup_functions

HLOOKUP(value, range, row, [rangelookup])
VLOOKUP(value, range, col, [rangelookup])
MATCH(value, range, [rangelookup])

index_function

INDEX(range, rownum, colnum)

countif_sumif_functions

COUNTIF(c1:c2,"criteria")
SUMIF(c1:c2,"criteria")

if_function

IF(cond,truevalue,falsevalue)

date_function

DATE(year,month,day)

time_function

TIME(hour,minute,second)

dmy_function

DAY(date)
MONTH(date)
YEAR(date)
WEEKDAY(date, [type])

hms_function

HOUR(datetime)
MINUTE(datetime)
SECOND(datetime)

exact_function

EXACT(v1,v2)

string_functions

FIND(key,string,[start])
LEFT(string,[length])
MID(string,start,length)
PROPER(string)
REPLACE(string,start,length,new)
REPT(string,count)
RIGHT(string,[length])
SUBSTITUTE(string,old,new,[which])
TRIM(string)
UPPER(string) - see Spreadsheet::Engine::Function::UPPER
LOWER(string) - see Spreadsheet::Engine::Function::LOWER
LEN(string) - see Spreadsheet::Engine::Function::LEN

is_functions

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISTEXT(value)

ntv_functions

N(value)
T(value)
VALUE(value)

math1_function

ABS(value)
ACOS(value)
ASIN(value)
ATAN(value)
COS(value)
DEGREES(value)
EVEN(value)
EXP(value)
FACT(value)
INT(value)
LN(value)
LOG10(value)
ODD(value)
RADIANS(value)
SIN(value)
TAN(value)

math2_function

ATAN2(x, y)
MOD(a, b)
POWER(a, b)
TRUNC(value, precision)

log_function

LOG(value,[base])

round_function

ROUND(value,[precision])

and_or_function

AND(v1,c1:c2,...)
OR(v1,c1:c2,...)

not_function

NOT(value)

choose_function

CHOOSE(index,value1,value2,...)

columns_rows_function

COLUMNS(c1:c2)
ROWS(c1:c2)

zeroarg_functions

ERRCELL()
FALSE()
NA()
NOW()
PI()
TODAY()
TRUE()

ddb_function

DDB(cost,salvage,lifetime,period,[method])

Depreciation, method defaults to 2 for double-declining balance.

See: http://en.wikipedia.org/wiki/Depreciation

sln_function

SLN(cost,salvage,lifetime)

Depreciation for each period by straight-line method

See: http://en.wikipedia.org/wiki/Depreciation

syd_function

SYD(cost,salvage,lifetime,period)

Depreciation by Sum of Year's Digits method

interest_functions

FV(rate, n, payment, [pv, [paytype]])
NPER(rate, payment, pv, [fv, [paytype]])
PMT(rate, n, pv, [fv, [paytype]])
PV(rate, n, payment, [fv, [paytype]])
RATE(n, payment, pv, [fv, [paytype, [guess]]])

Following www.openformula.org and ODF formula specification:

PV = - Fv - (Payment * Nper) [if rate equals 0]
Pv*(1+Rate)^Nper + Payment * (1 + Rate*PaymentType) * ( (1+Rate)^nper -1)/Rate + Fv = 0

For each function, the formulas are solved for the appropriate value (transformed using basic algebra).

npv_function

NPV(rate,v1,v2,c1:c2,...)

irr_function

IRR(c1:c2,[guess])

text_function

PLAINTEXT

html_function

HTML

HELPERS

field_to_colnum

$colnum = field_to_colnum(\@sheetdata, $col1num, $ncols, $row1num, $fieldname, $fieldtype)

If fieldname is a number, uses it, otherwise looks up string in cells in row to find field number

If not found, returns 0.

HISTORY

This is a Modified Version of SocialCalc::Functions from SocialCalc 1.1.0

COPYRIGHT

Portions (c) Copyright 2005, 2006, 2007 Software Garden, Inc. All Rights Reserved.

Portions (c) Copyright 2007 Socialtext, Inc. All Rights Reserved.

Portions (c) Copyright 2007 Tony Bowden

LICENSE

The contents of this file are subject to the Artistic License 2.0; you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.perlfoundation.org/artistic_license_2_0