Using Formulas in your Document Templates
- Allison Derome (Unlicensed)
- Shonal Singh
Formulas/calculations in word are a bit more advanced and can take a bit of time getting used to it. There are some great tutorials online but the best way to learn is by practicing.
Some key things to remember:
-Always use the Insert Field functions
-Never type anything directly into the Mergefield/always use the Edit Field function (an exception to this will be some special characters such as Asterisks(*), Forward Slash(/),Plus(+) etc.)
-Copy and paste will not work, you must use the Insert Field and Edit Field commands in Word
-Always upload the template and test all the values
-For more complex formulas, it’s best to map out what you need before you start
-Some formulas may result in a “Syntax Error!” or “REF#” especially in the Extras section, in this situation, you may have to add additional Mergefields (such as OptionsRRP) to counteract this. Additionally you may also have to use { IF } statements referencing Inventory Group Codes e.g. { IF {MERGEFIELD InventoryGroupCode \* MERGEFORMAT}=”ROLL” {MERGEFIELD RRP \*MERGEFORMAT}\*MERGEFORMAT}
Why do you need formulas in document templates?
BUZ has a logic that ensures all values match and are correct. Because of this, as an example, the RRP and Amount is always multiplied by the Quantity.
Sometimes we need to show the individual unit price and this is where formulas need to be used.
The most common formulas you would use are (+, - , =, /,*) and IF statement Mergefields.
Here are some helpful Mergefield formatting codes
\* Caps | Capitalizes the first letter of each word. |
\* FirstCap | Capitalizes the first letter of the first word. |
\* Upper | Capitalizes all letters. |
\* Lower | All letters are in lower case. |
0 (zero) | Specifies the minimum numeric places to display including leading zeroes. EXAMPLE
|
, (comma) | Places a digit grouping symbol (thousand separator) as specified in the regional settings in the Control Panel. EXAMPLE
|
. (decimal point) | Places a decimal point in its position. |
() (parenthesis) | Places negative number within parenthesis and a hyphen in place of zero. EXAMPLE
|
# | Specifies the minimum numeric places to display. If a place has no digit, it is replaced with a space. EXAMPLE
|
+ (plus) | Places a plus sign to a positive result, a minus sign to a negative result, or a space if the result is zero. EXAMPLE
|
AM/PM | Displays AM or PM. |
h or H | Hours. Lower case h denotes 12-hour format and upper case H denotes 24-hour format. Use hh or HH to display a leading zero on single digit hours. |
m | Minutes. Use mm to display a leading zero on single digit minutes. |
s | Seconds. Use ss to display a leading zero on single digit seconds. |
x | Does not display digits to its left and if used to the right of a decimal point, the result is rounded to that place. EXAMPLE
|
| Places a minus sign on a negative result or adds a space if the result is positive or zero. EXAMPLE
|
\b | Specifies text to be inserted before the merge field if the field is not blank |
\f | Specifies text to be inserted following the merge field if the field is not blank |
Video example of how to work with Mergefield formulas
Brief summary of how to use formulas:
Open Word document
Press Ctrl and F9 on your keyboard – this will create the expression field { }. For Mac users with Office 365 use FN + CMD + F9
Click inside the expression field and insert your merge field
Right-click on the mail merge field and select ‘Toggle Field Codes’
This will display the merge field nested inside an expression field
From here you can do the calculations on the merge field.
For example, to add 1 to the age field place an equals symbol (=) before the between the two open brackets and plus 1 (+ 1) between the two close brackets.
For example : to display 50% of the total amount in Quote Document { = { Mergefield TotalIncGST \*MERGEFORMAT } /2 } \*MERGEFORMAT} or { = { Mergefield TotalIncGST } /2 }
Press Alt+F9 to switch to Normal view
Save document and upload