Using Math Expressions

Math expressions represent special mathematical features that you can use in formulas. You can use the following math expressions:

  • WT() – Inserts the current Working Total quantity into the formula. For example:

    • WT()* 3 is the Working Total multiplied by 3

    • (WT()+B1)/4 is the Working Total added to quantity in cell B1 and the resulting sum divided by 4

  • VAL(a) – Converts a character value in an option list to a numeric value that will calculate in a formula. Character lists can easily be created in a formula spreadsheet cell using a series of commas or semicolons. If you made a formula that required the user to "Enter the Spacing on Center," a list of standard or common spacing values could be formed by entering 12 Inches O.C., 18 Inches O.C., 24 Inches O.C., 36 Inches O.C. into the spreadsheet cell that will be used in the formula equation. The VAL(a) feature will then convert each character string to its numeric value, that is, (12), (18), (24), and so on. for the formula calculation. For example:

    • WT()* VAL(B1) is the Working Total multiplied by the numeric value selected from the option list in spreadsheet cell B1

  • ROUND(a,b) – Rounds the quantity produced by a formula (variable "a") to the nearest whole number using the number of decimal places specified in parameter "b". For example:

    • ROUND(1234.5678,0) equals the value rounded up or down to the nearest whole number - 1235

    • ROUND(1234.5678,2) equals the value rounded to two decimal places - 1234.57

  • ROUNDUP(a,b) – Rounds quantity produced by a formula (variable "a") up to the nearest whole number using the number of decimal places specified in parameter "b". For example:

    • ROUNDUP(1234.5678,0) equals the value rounded up to the nearest whole number - 1235

    • ROUNDUP(1234.5678,2) equals the value rounded up to two decimal places - 1234.57

  • NOT(a) – If the parameter "a" is zero, then a value of 1 is returned, otherwise a value of 0 is returned. For example:

    • NOT(8) equals a value of 0

    • NOT(0) equals a value of 1

  • NEG(a) – If parameter "a" is negative, then a value of 1 is returned, otherwise a value of 0 is returned. For example:

    • NEG(-9) equals a value of 1

    • NEG(5) equals a value of 0

  • ISEMPTY(a) – If parameter "a" is an empty cell, then a value of 1 is returned, otherwise a value of 0 is returned. For example:

    • ISEMPTY(B1) equals a value of 1 if cell B1 is empty

    • ESEMPTY(B2) equals a value of 0 if cell B1 contains any integer

  • IF(a,b,c) – Parameter "a" represents a Relational Expression. If the result of "a" is non-zero, then the value of "b" is returned. If the value of "a" equals zero, then the value of "c" is returned. A Math Operator may also be entered as parameter "a". For example:

    • IF(0,1,2) equals a value of 2

    • IF(1,1,2) equals a value of 1

    • IF (3<4,5,8) equals a value of 5

  • ABS(a) – The absolute value of value "a" is returned. For example:

    • ABS(2) equals a value of 2

    • ABS(-2) equals a value of 2