Newest functions Upload a function Leave a tip My account

There are 2,522 functions in this library.

ValueArithmetic ( List1 ; List2 ; operation )

Does basic arithmetic between two value lists, doing one operation (add, subtract, multiply, divide, raise) at a time

  Average rating: 5.0 (1 vote) Log in to vote

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

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

/* ======================= # ValueArithmetic ( List1 ; List2 ; operation ) # Purpose: Do arithmetic calculations on corresponding members of two value lists # Parameters: # List1: ¶-delimited value list # List2: ¶-delimited value list # operation: "*", "/", "+", "-", "^" (choose only one) # other valid ways to denote operation, must be quoted, since they are text: # times, x, divide, divided, divided by, plus, add, minus, subtract, raise, power # # 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, concatenate 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: ValueArithmetic ( "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) # # ValueArithmetic ( "8¶7¶6¶5" ; "6¶5¶0¶20" ; "/" ) # Result: 1.333...¶1.4¶?¶.25 --note: divide by 0 gives ? # # ValueArithmetic ( "8¶7¶6¶5" ; 2 ; "^" ) # Result: 64¶49¶36¶25 --note: acts like Extend # # ValueArithmetic ( "8¶7¶6¶5" ; "" ; "^" ) # Result: "" --an empty value list results in an empty result # # Written by Bill Thurmes, MDCA # Creation Date: 10/20/2016 # Changed from ValueMath to ValueArithmetic 2/16/2019 ============================*/ 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 ; ¶ & ValueArithmetic ( List1 ; RightValues ( List2 ; N - 1 ) ; z ) ; s = 2 ; ¶ & ValueArithmetic ( RightValues ( List1 ; N - 1 ) ; List2 ; z ) ; ¶ & ValueArithmetic ( MiddleValues ( List1 ; 2 ; N - 1) ; MiddleValues ( List2 ; 2 ; N - 1) ; z ) )//end Case )//end Let Click to copy

Will take two value lists and perform an arithmetic operation between the nth value of each. For example, when the operation is addition, the first values of each list will be added together to give the first value of the resultant list. Five types of operations are accepted, and each is denoted by a bit of text: "+", "-", "*", "/", "^". Some other names for the operations are accepted.
The two value lists should have the same number of values. Two exceptions:
If one 'list' has only a single value, this function will work like Extend, so all values of the other list will be treated by the operation with the single value
If one list has more (2+) values than the other, the longer list will be trimmed to the same number of values as the other (so make sure you enter the values correctly!).

An empty value will be treated as a 0.
A divide by zero error will give a '?' for that value in the resultant list

Note that operations are MUCH faster when things are all done in memory (such as is done here, between two lists) than when done in fields, where read/write operations slow everything down

 

Comments

Log in to post comments.

 

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: