The Feed Doctor: Show Me The Money
This week, “M.” in Fond du Lac, WI, writes:
Dear Feed Doctor,
Just as gas stations do, I calculate my items’ prices to 3 decimal places. However, most shopping engines only want 2 decimal places. What should I do?
M. is right; most shopping engines want prices with exactly two digits to the right of the decimal point—no more, no less. They also usually don’t want a currency symbol. The FORMATCURRENCY function will (surprise) remove the currency symbol and fix the number of digits to the right of the decimal point. Here’s how you might write the rule: FORMATCURRENCY($ITEMBINPRICE), and here are some example results (for a US customer; for a European customer, the currency symbol and separator characters would of course be different):
| Example # | Price Before | Price After |
|---|---|---|
| 1 | 12345.12000 | 12345.12 |
| 2 | 0.009 | 0.01 |
| 3 | 12345 | 12345.00 |
| 4 | 1,234.567 | 1234.57 |
| 5 | 1,00,00.00 | 10000.00 |
| 6 | $19.99 | 19.99 |
A couple of things to note:
- As examples #1 and #2 show, the function rounds to the nearest penny (0.009 becomes 0.01).
- As example #3 shows, the function will add trailing zeros to “fill out” to exactly 2 decimal places.
- As examples #4 and #5 show, the function will remove any commas. In fact, the commas are completely ignored.
- As example #6 shows, the function will remove the currency symbol.
Now here’s the really cool part: just as I mentioned in last week’s post, for most default templates, we already have a rule in place that will format the prices properly. So, once again, M., the answer is: we already did it for you.