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

%function_list

  $Spreadsheet::Engine::function_list{MYFUN} = [ \&my_function, ARGCNT ];

To add a new function provided, you should extend the %function_list hash. The key should be the name of the function as called from within the spreadsheet, and the value should be a reference to a list containing a reference to the subroutine providing your functionality, and information on how many arguments should be passed:

   0 = no arguments
   >0 = exactly that many arguments
   <0 = that many arguments (abs value) or more
   100 = don't check

Your function should take the arguments $fname, \@operand, \@foperand, \$errortext, \%typelookup, \%sheetdata.

(It is high on the TODO list to provide a better interface to all this.)

EXPORTS

calculate_function

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

FUNCTION providers

series_functions

AVERAGE(v1,c1:c2,...)

COUNT(v1,c1:c2,...)

COUNTA(v1,c1:c2,...)

COUNTBLANK(v1,c1:c2,...)

MAX(v1,c1:c2,...)

MIN(v1,c1:c2,...)

PRODUCT(v1,c1:c2,...)

STDEV(v1,c1:c2,...)

STDEVP(v1,c1:c2,...)

SUM(v1,c1:c2,...)

VAR(v1,c1:c2,...)

VARP(v1,c1:c2,...)

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])

LEN(string)

LOWER(string)

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)

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