All things TrackVia!

Read, write, comment.

This is a public Category  publicRSS

Archived FAQ

    • How do I use numeric formulas?
      Archived FAQ posted 12/19/08 by Admin, last edited Aug 05 by Matt Strenz, tagged Field Types, General
      464 Views, 0 Comments
      question:
      How do I use numeric formulas?
      answer:

      Numeric calculations can be used in any calculated field type. For example, you could create a calculated short answer field whose formula, which produces text, is

      "You will retire in only " & (65 - age) & " more years!"

      Likewise, text fields (or any other field type) can be used in numeric calculations. If you have a calculated number field whose formula is

       100 + (first name / 4)

      then in any database record where First Name actually contains a number, the result will be 100 plus that number divided by four. This is generally not a good idea, though. If First Name contains "Bob," your formula will treat Bob as a zero, producing a result of 100. And inside certain numeric functions, like round, using Bob in place of a number will produce an error. TrackVia will set the calculated value to (none), and add an explanation of the error to the record's change history.

      Percentages. In formulas, percentages function like their numeric equivalent: 5% is treated as 0.05, 50% is treated as 0.5, and 500% is treated as 5.0. Therefore, you should not divide a percentage value by 100 in the formula. This is true for percentage fields in your database as well as fixed percentages in the formula. For example, suppose you're calculating sales tax on a purchase, and you have a fixed 6% state tax rate plus a variable county rate. Your formula might be

      purchase price * (6% + county rate)

      Similarly, in a calculated percentage field, a formula result of 0.5 will be displayed as 50%. A good formula for year-over-year sales growth might be

      (2007 sales - 2006 sales) / 2006 sales

      If the formula produces a result of 0.05, it will display as 5%, and you shouldn't divide by 100 in the formula.

      Be careful when using the round function on percentages. If you want to round a result of 10.594% to 10.6%, you should not round it to 1 digit after the decimal, but rather 3:

      round( some percentage value, 3)

      because internally TrackVia thinks 10.6% is 0.106.

      Built-in functions. TrackVia's built-in numeric functions are shown below. In the table, X, Y, and Z are inputs that the function requires. In your formulas, these should be numbers or database field names.

      Function or operator Description
      X + Y
      X - Y
      X * Y
      X / Y
      		 Addition
      Subtraction
      Multiplication
      Division
      X ** Y or
      X ^ Y      		 Exponentiation, that is, A to the power of B
      sqrt(X)
      fact(X)
      abs(X)      		 Square root of X
      Factorial of X
      Absolute value of X
      In the following six functions, blank inputs are ignored. That is, the average of 4, 6, and (none) is 5.
      min(X,Y,Z,...)
      max(X,Y,Z,...)
      sum(X, Y, Z, ...)
      count(X, Y, Z, ...)
      average(X, Y, Z, ...)
      median(X, Y, Z, ...)      		 Minimum of X, Y, Z, ... (takes 1 or more inputs)
      Maximum of X, Y, Z, ...
      Sum of X, Y, Z, ...
      The number of non-blank inputs
      Average (arithmetic mean) of X, Y, Z, ...
      Median (middle value) of X, Y, Z, ...
      round(X)
      round(X,Y)      		 Round X to the nearest integer
      Round X to Y digits after the decimal point
      round_down(X)
      round_up(X)      		 Round X down to nearest integer (toward zero)
      Round X up to nearest integer (away from zero)
      floor(X)
      ceil(X)      		 Round X down to nearest integer (toward negative infinity)
      Round X up to nearest integer (toward positive infinity)
      int(X)
      frac(X)      		 Integer part of X (part before the decimal point)
      Fractional part of X (part after the decimal point)
      remainder(X,Y) or
      modulo(X,Y)      		 Remainder of the division of X by Y
      exp(X)
      ln(X)
      log10(X)      		 Exponential of X (e to the power of X)
      Natural logarithm of X (base e)
      Common logarithm of X (base 10)
      sin(X)
      cos(X)
      tan(X)      		 Sine of X
      Cosine of X
      Tangent of X
      degrees(X)
      radians(X)      		 Number of degrees in an angle of X radians
      Number of radians in an angle of X degrees
      pi() The constant pi, 3.14159... (takes no inputs)
      rand()
      rand(X)
      rand(X,Y)      		 Random number between 0 and 1
      Random number between 0 and X
      Random number between X and Y
      The following financial functions work as in Microsoft Excel TM. The parameters in square brackets are optional.
      pmt(rate,nper,pv,[fv],[type])
      pv(rate,nper,pmt,[fv],[type])
      fv(rate,nper,pmt,[pv],[type])
      nper(rate,pmt,pv,[fv],[type])
      ipmt(rate,per,nper,pv,[fv],[type])
      		 the payment per period of a loan or annuity
      the present value of a loan or annuity
      the future value of a loan or annuity
      the number of periods for a loan or annuity
      the interest portion of a payment in a given period
      distance_bt_zips(X,Y) If X and Y are valid 5-digit or 5+4 US Zip codes, returns the distance in miles between their centers
      Need more? Let us know!

      If you need a function that's not on the list, please contact us!  We'd be happy to try and add it for you.