List.Math ( )

Function stats

Average user rating
29
204
9999
Support
FileMaker 7.0 +
Date posted
15 February 2009
Last updated
20 February 2009
Version
Recursive function
No

Author Info
 Genx

18 functions

Average Rating 4.1

author_avatar



 

Function overview

Prototype

List.Math  ( FunctionName;   ListValues )


Parameters

FunctionName  The Math Function To Call


ListValues  The list of Values


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

*/
// ===================================

 

Comments

Fabrice
16 February 2009



Hi Alex,
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
     Feature request
Genx
16 February 2009



Thanks :)

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 )
  General comment
Fabrice
16 February 2009



I think if you have only one value and functionname is max, min..., then simply return ListValues (maybe you should add a GetAsNumber (not for List though).
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 )
  General comment
Genx
16 February 2009



I'm glad one of us is thinking :)

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?
  General comment
Fabrice
17 February 2009



Thinking, maybe, but certainly making the same mistake over and over again... try 3/2 :)
  General comment
Genx
17 February 2009



Done :) Thanks for making a decent function out of this!!
(Edited by Genx on 17/02/09 )
  General comment
BruceR
18 February 2009



Fails if the list has blank values in it. For instance I used applyToList to get some values matching a test and it returns empty lines for no match.
 Bug report
BruceR
18 February 2009



Looks like this does it:
Let( x=Substitute(
Filter(ListValues;"e-0123456789¶" & Middle ( 2/3 ; 2 ; 1 ));
["¶";";"] ; [";;"; ";\"\""] ) ;
 Bug report
sitekeepers
09 July 2014



Hi,
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
 Bug report