NAME

Language::FormulaEngine::Namespace::Default - Default spreadsheet-like set of functions and behavior

VERSION

version 0.08

DESCRIPTION

This is a namespace containing many spreadsheet-like functions. It aims for spreadsheet similarity rather than compatibility; the goal to give users of the FormulaEngine a familiar environmet rather than to try duplicating all features and misfeatures Excel.

Core Grammar Functionality

These are the methods that implement the infix operators.

sum( num1, num2 ... )

negative( num1 )

mul( num1, num2, ... )

div( numerator, denominator )

and( bool1, bool2, ... )

This applies perl-ish boolean semantics to each argument, and returns a numeric 0 or 1. No arguments are evaluated after the first false value.

or( bool1, bool2, ... )

This applies perl-ish boolean semantics to each argument, and returns a numeric 0 or 1. No arguments are evaluated after the first true value.

not( bool1 )

This applies perl-ish boolean semantics to the argument and returns numeric 1 or 0.

compare( val1, op, val2, ...op, val )

This compares two or more values against the 6 canonical operators "<", "<=", ">", ">=", "==", "!=" and returns 0 or 1.

It uses numeric comparison if both sides of an operator looks_like_number, and uses string comparison otherwise.

Utility Functions

choose( offset, val1, val2, val3, ... )

Given a 1-based offset, return the value of the Nth parameter.

if( condition, val_if_true, val_if_false )

If condition is "true" (Perl interpretation) return val_if_true, else val_if_false.

iferror( value_maybe_error, alternate_value )

If value_maybe_error does not throw an exception, return it, else return the alternate_value.

ifs( condition1, value1, condition2, value2, ... )

A simplified sequence of IF functions. If condition1 is true, it returns value1, else if condition2 is true it returns value2, and so on. If no condition is true it dies. (use a final true condition and value to provide a default)

na()

Throw an NA exception.

Math Functions

abs( number )

Return absolute value of number

acos( ratio )

Return angle in radians of the ratio adjacent/hypotenuse.

acot( ratio )

Return angle in radians of the ratio adjacent/opposite.

asin( ratio )

Return angle in radians of the ratio opposite/hypotenuse.

atan( ratio )

Return angle in radians of the ratio opposite/adjacent.

atan2( x, y )

Same as atan, but without division, so x=0 returns PI/2 instead of division error.

average( num1, ... )

Return sum of numbers divided by number of arguments

base( num1, radix, min_length=0 )

Return number converted to different base, with optional leading zeroes to reach min_length.

ceiling( number, step=1 )

Round a number up to the next multiple of step. If step is negative, this rounds away from zero in the negative direction.

cos( angle )

Cosine of angle in radians

cot( ratio )

Return the angle for the triangle ratio adjacent/opposite.

degrees( angle_in_radians )

Convert radians to degrees

exp( power )

Return base of the natural log raised to the specified power.

fact( n )

Compute factorial of n. (1 * 2 * 3 * ... n)

floor( number, step=1 )

Round a number down to the previous multiple of step. If step is negative, this rounds toward zero in the positive direction.

max( number, ... )

Return largest value in list

min( number, ... )

Return smallest value in list

mod( number, modulo )

Returns true modulous of a number. This uses Perl's (and math's) definition. For the Excel- compatible MOD function, see remainder.

pi()

Value of π

radians( angle_in_degrees )

Convert degrees to radians.

rand( range=1 )

Returns pseudo-random value greater or equal to 0 and less than range. This uses perl's (C's) built-in rand() function which is likely not as good as the generators used by spreadsheet programs, but I didn't want to add a hefty dependency.

remainder( number, divisor )

Return the number after subtracting the biggest multiple of divisor that can be removed from it. The remainder's sign will be the same as the sign of divisor (unless remainder is zero).

round( number, digits=0 )

Round NUMBER to DIGITS decimal places of precision. Uses the IEEE 5-round-to-even algorithm that C gives us. DIGITS defaults to 0, making it round to the nearest integer.

Dies if you attempt to round something that isn't a number.

roundup( number, digits=0 )

Like "round", but always round up. See also "ceiling".

rounddown( number, digits=0 )

Like "round", but always round down. See also "floor".

sign( value )

Return 1, 0, or -1 depending on the sign of value.

sin( angle )

Returns ratio of opposite/adjacent for a given angle in radians.

sqrt( number )

Return square root of a number.

tan( angle )

Return ratio of opposite/adjacent for an angle.

String Functions

char( codepoint_value )

Return a unicode character.

clean( string )

Returns string after removing all non-printable characters (defined as [:^print:] )

code( string )

Opposite of "char", known as ord() in other languages. Returns the unicode codepoint number of the first character of the string.

concat, concatenate( string, ... )

Returns all arguments concatenated as a string

find( needle, haystack, from_offset=1 )

Return the character offset of needle from start of haystack, beginning the search at from_offset. All offsets are 1-based.

fixed( number, decimals=2, no_commas=false )

Return the number formatted with a fixed number of decimal places. By default, it gets commas added in the USA notation, but this can be disabled.

len( string )

Return number of unicode characters in STRING.

lower( string )

Return lowercase version of STRING.

replace( string, offset, length, new_text )

Replace text in string with new_text, overwriting length characters from offset.

substr( string, offset, length=max )

Same as perl's builtin.

trim( string )

Remove all leading and trailing whitespace and replace runs of whitespace with a single space character.

upper( string )

Return uppercase version of STRING.

textjoin, join( separator, string, ... )

Same as perl's builtin.

DateTime Functions

Date math is implemented using the DateTime module. Strings are coerced into dates using the DateTime::Format::Flexible module for any parameter where a spreadsheet function would normally expect a date value. "Since 1900" date serial numbers are not used at all.

date( year, month, day )

Convert a (year,month,day) triplet into a date.

datedif( start_date, end_date, unit )

Calculate difference bwteen two dates. Unit can be one of: "Y" (whole years), "M" (whole months), "D" (whole days). Dates can be parsed from any string resembling a date.

datevalue( text )

Parse a date, or die trying.

day( date )

Returns the day number of a date

days( end_date, start_date )

Returns number of days difference between start and end date.

eomonth( start_date, months )

Calculate the date of End-Of-Month at some offset from the start date.

hour( date )

Return the hour field of a date.

minute( date )

Return minute field of a date.

month( date )

Return month field of a date.

year( date )

Return the year field of a date.

AUTHOR

Michael Conrad <mconrad@intellitree.com>

COPYRIGHT AND LICENSE

This software is copyright (c) 2023 by Michael Conrad, IntelliTree Solutions llc.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.