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

cpanm Spreadsheet::Engine

perl -MCPAN -e shell
install Spreadsheet::Engine