List.Math ( )
Function stats
Function overview
Prototype
Parameters
Description
Tags: Variance Sum StDevP Stdev Min Max Math List Count Average
Allows you to run the "Repeating / Related" math functions that normally only accept Repeating / Related input's - directly on a list.
Accepts each of the following (at least):
Average, Count, List, Max, Min, Stdev, StDevP, Sum, Variance, VarianceP
Credit for this one goes to Christian Jäger, of Hannover who uses this method to execute the Max function here - i've only just added the ability of being able to execute any of the functions:
http://www.briandunning.com/cf/757
http://www.hannover.de
Additional credit to Fabrice and Bruce for their suggestions in the comments below.
Please note that empty values are ignored e.g.
List.Math( "Average" ; "5¶1¶" ) = 3 where as List.Math( "Average" ; "5¶1¶0" ) = 2
Examples
Sample input
Sample output
Function code
Filter(ListValues;"e-0123456789¶" & Middle ( 2/3 ; 2 ; 1 ));
["¶";";"] ; [";;"; ";\"\""] ) ;
If( ValueCount( ListValues ) > 1 ;
Evaluate( FunctionName & "(" & x &")") ;
Case(
not IsEmpty(FilterValues( FunctionName ; "Sum¶Max¶Min¶Average" )) ; GetAsNumber(ListValues);
not IsEmpty(FilterValues( FunctionName ; "Stdev¶StDevP¶Variance¶VarianceP" )) ; 0 ;
ListValues
)
)
)
// ===================================
/*
This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports
please visit http://www.fmfunctions.com/fid/204
Prototype: List.Math( FunctionName; ListValues )
Function Author: Genx (http://www.fmfunctions.com/mid/29)
Last updated: 20 February 2009
Version: 2.2
*/
// ===================================
Login or register to comment
Create a new account with fmcustomfunctions.com or login to post a comment.
Related Functions
Comments
15 February 2009
it seems some situations might be problematic for this first version, among which :
- decimal separator is coma
- negative numbers (just add a - in the filter string)
- numbers with scientific notation (requires "e" in the filter string)
- only one item in the list for aggregate functions
15 February 2009
Re the only 1 item in the list, any ideas? Thinking maybe parse for the function name and return the appropriate value?
StDev, StDevP, Variance, VarianceP should return 0 - technically, StDev and Variance should return "?" but meh. Sum, Average, Min, Max should return the single value passed in... Count and List aren't really of any use...
Anything I'm missing?
(Edited by Genx on 15/02/09 )
15 February 2009
About decimal separator, I would suggest to really calculate it instead of just adding , and . in the filter. A number could be written 1,256.99
To get the separator, simply do Middle ( 2/3 ; 2 ; 1 )
16 February 2009
The decimal idea was quite sweet, but I don't get a zero returned in mine so I used left ; 1 - does that change b/w PC's / regions?
17 February 2009
17 February 2009
(Edited by Genx on 17/02/09 )
18 February 2009
18 February 2009
Let( x=Substitute(
Filter(ListValues;"e-0123456789¶" & Middle ( 2/3 ; 2 ; 1 ));
["¶";";"] ; [";;"; ";\"\""] ) ;