**Function stats**

FileMaker 7.0 +

15 February 2009

20 February 2009

2.2 **+ View history**

No

** 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**

List.Math( "Variance" ; "1¶3¶4" )

**Sample output**

2.3333333

**Function code**

Let( x=Substitute(

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

*/

// ===================================

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

*/

// ===================================

## Comments

Fabrice16 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

Genx16 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 16/02/09 )

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

Genx16 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?

Fabrice17 February 2009

Genx17 February 2009

(Edited by Genx on 17/02/09 )

BruceR18 February 2009

BruceR18 February 2009

Let( x=Substitute(

Filter(ListValues;"e-0123456789¶" & Middle ( 2/3 ; 2 ; 1 ));

["¶";";"] ; [";;"; ";\"\""] ) ;

sitekeepers09 July 2014

I have been trying to utilise this function along with related.sumif and every time i get same result: "sum" in the calculation field.

I have removed all custom functions and setup only this one List.Math trying to test it with the sample input of:

List.Math( "Variance" ; "1¶3¶4" )

and even this brings only a "Variance" in the calculation field.

What am I doing wrong, any help?

The lack of SUM IF in FM is really driving me crazy. I have done few days research on this subject now, and gentleman, you are my last hope.

Kindest Regards