Formula 13
Operators/Functions Used
ToText, Truncate, Division (/), Concatenate (+), Remainder
Formula Purpose
Bulk grains and nuts are inventoried by the ounce, but management wants to see a breakdown in pounds and ounces on the inventory report.
Formula
ToText((Truncate({file.OZ}/16)),0) + " pounds, " +
ToText((Remainder ({file.OZ}, 16)),0) + " ounces "
Result
Ounces
| Resulting Text
|
433
| "27 pounds, 1 ounces"
|
278
| "17 pounds, 6 ounces"
|
1455
| "90 pounds, 15 ounces"
|
Explanation
- The formula uses the Divide operator (/) to divide {file.OZ} by 16, thus calculating the number of pounds of the item in inventory. Unless {file.OZ} is perfectly divisible by 16, the quotient will be a whole number with decimal places.
- The Truncate function returns only the whole number (integer) part of the quotient.
- The ToText function converts the number to text so it can be used in a string with other text, and formats the number to zero decimal places (the second parameter).
- The formula uses the Concatenate operator (+) to connect the number of pounds with the text string " pounds" to give the new string " n pounds" (where n is the calculated number of pounds).
- The Remainder function determines the remainder that results from the division {file.OZ}/16 ({file.OZ} the numerator, 16 the denominator). The remainder is a number of ounces less than 16 (less than a full pound).
- The ToText function converts the remainder to text so it can be included in the final text string, and formats the number to zero decimal places.
- The Concatenate operator (+) connects the number of ounces (now converted to text, to the string before ("n pounds,") and to the string after ("ounces") to produce the final text string "n pounds, y ounces" (where y is the remainder).
Related topics
Formulas In Action