I have a formula that works most times but other times it doesn’t work. The idea is I have a table where I’m recording purchase orders and I have a formula that verifies the amount on the invoice matches the amount on the purchase order. So I have quantity ordered (Qty), unit cost (Unit$), a formula field to show the extended amount which is Qty*{Unit $}. I have the invoice amount and then I have another formula that let’s me know if the amount is verified or not which is IF(Invoice=Extended,“ :heavy_check_mark: ”,“ :no_entry: ”). I show two examples of where one works and one does not. Should my formula be something different? Thanks in advance!
Formula works sometimes
In your formula for the “Extended” field, wrap the entire output in a ROUND() function like this:
ROUND(
>>Your Formula Here<<,
2
)
The issue you are experiencing has to do with the fact that the numbers behind the scenes are not the same as the “formatted display” you get when you tell it to show you the output of “Extended” as “Currency”. It rounds and formats the numbers behind the scenes for the purpose of displaying currency to you, but it does not actually save the number as a rounded currency value — behind the scenes, the “Extended” field is still holding a value that goes out to however many decimal places it needs to in order to hold the accurate value of the formula you wrote.
So when you make that comparison in the “Amt. Ver.” Field, what you are getting is a comparison between a currency value that actually ends at 2 decimal places (Invoice) and a decimal value that goes out to who knows how many places (Extended) — and the two will frequently not be exactly the same.
In your formula for the “Extended” field, wrap the entire output in a ROUND() function like this:
ROUND(
>>Your Formula Here<<,
2
)
The issue you are experiencing has to do with the fact that the numbers behind the scenes are not the same as the “formatted display” you get when you tell it to show you the output of “Extended” as “Currency”. It rounds and formats the numbers behind the scenes for the purpose of displaying currency to you, but it does not actually save the number as a rounded currency value — behind the scenes, the “Extended” field is still holding a value that goes out to however many decimal places it needs to in order to hold the accurate value of the formula you wrote.
So when you make that comparison in the “Amt. Ver.” Field, what you are getting is a comparison between a currency value that actually ends at 2 decimal places (Invoice) and a decimal value that goes out to who knows how many places (Extended) — and the two will frequently not be exactly the same.
Ah, you’re the best! Thank you. I had a feeling it had to do with something like that but I wasn’t sure which formula to use. Works perfectly now, thanks again :slightly_smiling_face:
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.