ValueMath ( List1 ; List2 ; operation )

Do mathematical calculations on corresponding members of two value lists

Average rating: 3.3 (4 votes) Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

ValueMath ( "8¶7¶6¶5" ; "6¶5¶20¶20¶20¶20¶14¶0¶5¶10¶10" ; "*" )
ValueMath ( "8¶7¶6¶5" ; "6¶5¶0¶20" ; "/" )
ValueMath ( "8¶7¶6¶5" ; 2 ; "^" )
ValueMath ( "8¶7¶6¶5" ; "" ; "^" )

Sample output:

48¶35¶120¶100
1.333...¶1.4¶?¶.25 --note: divide by 0 gives ?
Result: 64¶49¶36¶25 --note: acts like Extend
Result: "" --an empty value list results in an empty result

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/* =======================
# ValueMath ( List1 ; List2 ; operation )
# Purpose: Do mathematical calculations on corresponding members of two value lists
# Parameters:
# List1: ¶-delimited value list
# List2: ¶-delimited value list
# operation: "*", "/", "+", "-", "^" (choose only one)
#
# Use two value lists and multiply/divide/add/subtract/(raise to power) them together, value position by value position
# If one List is only one member long, this function will work like Extend, i.e., apply that value with operation to all members of other list
# If both lists are >= 2 members long but are not the same length, concatonate the longer list
#
# Recursive
#
# Logic:
# check how many values are in each list (n1 & n2 for List1 and List2)
# Case
#	n1 = n2 => continue operation
#	n1 = 1 or n2 = 1 => do operation with single item and value list items (like Extend for fields)
#	n1 <> n2 => do operation with the first pair, but only pass on the same number of values to the next iteration
#		need to do this because otherwise part of the result would be (with *) list1 value * list2 value, then extend
#	n1 or n2 = 0 ; do nothing
# Then ( GetValue ( List1 ; 1 ) [operation] GetValue ( List2 , 1 ) )
#
# Examples: ValueMath ( "8¶7¶6¶5" ; "6¶5¶20¶20¶20¶20¶14¶0¶5¶10¶10" ; "*" )
# 	Result: 48¶35¶120¶100  --note: one list much longer than other; other is trimmed to same ValueCount
#						(alternative would be to have final value in shorter list act like Extend)
#	
#	ValueMath ( "8¶7¶6¶5" ; "6¶5¶0¶20" ; "/" )
#	Result: 1.333...¶1.4¶?¶.25  --note: divide by 0 gives ?
#	
#	ValueMath ( "8¶7¶6¶5" ; 2 ; "^" )
#	Result: 64¶49¶36¶25   --note: acts like Extend
#	
#	ValueMath ( "8¶7¶6¶5" ; "" ; "^" )
#	Result: ""   --an empty value list results in an empty result
#	
# Written by Bill Thurmes, MDCA
# Creation Date: 10/20/2016
============================*/
	
Let ( [
	n1 = ValueCount ( List1 ) ;
	n2 = ValueCount ( List2 ) ;
	s = Case ( n1 = 0 or n2 = 0 ; 0 ; 
				n1 = 1 ; 1 ; 
				n2 = 1 ; 2 ; 
				0 ) ;
				
	N = Case ( s = 1 ; n2 ; 
				s = 2 ; n1 ; 
				n1 >= n2 ; n2 ; 
				n1 ) ;
				
	z = Case ( operation = "*" or operation = "times" or operation = "x" ; "*" ;
				operation = "/" or operation = "divide" or operation = "divided" or operation = "divided by" ; "/" ;
				operation = "+" or operation = "plus" or operation = "add" ; "+" ;
				operation = "-" or operation = "minus" or operation = "subtract" ; "-" ;
				operation = "^" or operation = "raise" or operation = "power" ; "^"
			)
	
	;//just to make the following statements shorter
	N = Case ( Length ( Filter ( z ; "*/+-^" ) ) = 1 ; N ; 0 ) ; 
	Result = Case ( N = 0 ; "" ;
				z = "*" ; GetAsNumber (  GetValue ( List1 ; 1 ) * GetValue ( List2 , 1 ) ) ;
				z = "/" ; GetAsNumber (  GetValue ( List1 ; 1 ) / GetValue ( List2 , 1 ) ) ;
				z = "+" ; GetAsNumber (  GetValue ( List1 ; 1 ) + GetValue ( List2 , 1 ) ) ;
				z = "-" ; GetAsNumber (  GetValue ( List1 ; 1 ) - GetValue ( List2 , 1 ) ) ;
				z = "^" ; GetAsNumber (  GetValue ( List1 ; 1 ) ^ GetValue ( List2 , 1 ) ) ;
				GetAsNumber ( Evaluate ( GetValue ( List1 ; 1 ) & operation & GetValue ( List2 , 1 ) ) ) 
				) //end Case
	] ;
	
	Result &  
	Case ( n1 = 0 or N = 0 ; "" ; 
			s = 1 ; ¶ & ValueMath ( List1 ; RightValues ( List2 ; N - 1 ) ; z ) ;
			s = 2 ; ¶ & ValueMath ( RightValues ( List1 ; N - 1 ) ; List2 ; z ) ;
			¶ & ValueMath ( MiddleValues ( List1 ; 2 ; N - 1) ; MiddleValues ( List2 ; 2 ; N - 1) ; z )
		)//end Case
)//end Let

Use two value lists and multiply/divide/add/subtract/(raise to power) them together, value position by value position
The result is a new value list with the results of each math manipulation
Note: If one List is only one member long, this function will work like Extend, i.e., apply that value with operation to all members of other list
Note: If both lists are >= 2 members long but are not the same length, concatenate the longer list

I later (Feb 2019) renamed this ValueArithmetic because it was a better description of what the CF does

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

ValueMath ( List1 ; List2 ; operation )

Comments

Support this website.

Support this website.