Currency and formulas aren't correct

1

Hihi,

I’m maybe struggling with the format or formula and would like some assistance if at all possible.

Perhaps I have the formula setup wrong?

I have a few fields
Order Quantity (Number)
Product Cost (Currency)

Product Markup (Percentage)

Product Price (Formula)

(Sumproduct(Product Cost$,Product Markup%)+Sum(Product Cost$)

Total Product Price
product(Product Price, Order Quantity)

In the example:
Order Quantity 50
Product Cost $1.50
Product Markup 15%
Product Price formula is correct and outputs $1.73 (instead of 1.7250)
When I hover over $1.73 it shows $1.725 (USD)

However, when I do the total product price, it outputs $86.25 which derives from($1.50 + 15% = 1.7250)

Instead of 1.73 * 50 = $86.50

I’ve made the format currency with 2 decimal places on all the currency fields, however this issue is still occuring.

Any suggestions would be greatly appreciated as it’s been frustrating

I’ve included a copy of the doc here that’s editable:

2

This behavior is normal: formatting is for the eye only, the real value is used. If you want to use the rounded value, you need to use the function round(). Round will ensure you are saving the rounded value and the calculations that follow will use the new rounded value.

To illustrate the effect I changed the product price column to 4 decimal places so you can see what is going on.

2 Likes
3

Thanks so much!! Sorry about that mistake, didn’t realize formatting was eye only.

Appreciate you adding the round function for me. I’ll be sure to include that in all formulas related to currency