Rounding Errors

[WilliamF]

I have browsed through this entire Forum and was surprised to find no mention anywhere about possible Rounding Errors. I am using FM5 on both the Mac and PC.

There are times when FM does not Round correctly to the nearest cent when using the dollars and cents format. This seems to occur when using a calc that first (Sums) the contents of several (Rounded) data Fields and produces a (Rounded) result. It has been of from 1 cent to 3 cents. This is in a DB developed for a county govt using valuations and tax levies and errors just can't be tolerated. Has anyone had this problem? If so, has anyone found a solution to it. The solutions given in FM Tech do not fix the problem. The machine running this DB is on a NT Server which may or may not be part of the problem. Would much appreciate any input any of you could provide as I am past the head scratching stage.

[Chuck]

Are you able to track down exactly where the errors are occuring in the sequence of calculations?

I have never come across rounding errors with FileMaker, although I have come across rounding problems. The method I've used to solve the problems is to make sure that I round in every calculation.

Also, be sure that the problem is not just a display problem, but an actual mathematic anomaly. For instance, multiplying $1.00 by 8.25% (the local sales tax rate) actually gives a product of $0.0825. If you display this in a field and use the display options to give the total to the nearest cent, you will see $0.08. But FMP actually has 0.0825 in the contents of the field. Add four of these together, and FMP will come up with $0.33, even though each of the four instances displayed as 0.08, which you would think would multiply by four to 0.32, giving you a one cent difference.

The solution would be to use the Round function in each instance. That way FileMaker actually uses 0.08 instead of 0.0825. For the example above, the calc would be something like:

Round( Amount * Tax_Rate, 2 )

Chuck

[WilliamF]

Chuck:

Thanks for taking the time to assist.

I appreciate the difference between "errors" and "problems" and will take another look. The FM Tech's thought it might be an NT problem, but I somewhat doubt that.

Thanks

WilliamF

[Anatoli]

Server is just hosting the data, all processing and calculations and rounding is done in Client.

[WilliamF]

Concerning Rounding (Problems) in Filemaker 5.

Thanks to both gentlemen who replied to my query.

I have the problem apparently fixed. There are a jillion calc fields in this solution. I made each of them Round to (4). The only fields that are rounded to (2) are those that must show the actual dollars and cents that are owed by the Utility company.

A few thoughts about this: It appears to me at this time that FM 5 is very accurate in in it's calculations. However, when you Round to (2), it continues to work with the raw data, not the rounded data. From the view-point of total accuracy, that is great. From the view-point of practical application, that is not so great. If you wish to show a number of Fields in dollar and cents form, rounded to (2), then the grand total of those fields will likely not be the same as you would get if you added those rounded fields by a calculator. When you are sending out statments to large corporations who are paying a large chunk of taxes, you can logically expect them to double check your math on your statement. If it doesn't match, you have a problem. Don't know the answer to this problem, but would think that FM could perhaps program so that the grand totals reflect the ACTUAL amount as shown in the Rounded Fields, not the raw data. That seems to me to be more (actual world ) appropriate than the way it seems to be now.

I may be way off base on this and would like to hear from any others who have like or different opinions.

Thanks

WilliamF

[Chuck]

Personally, I think I prefer the way FileMaker handles this. If you want perfect accuracy, you can get it by not rounding and showing all the digits of the number. If you want the computer to be accurate, but don't want to see seventeen digits, you can do that with display options. And if you want to round numbers to a certain digit and have the computer work with those rounded numbers, you can do that.

It actually makes more sense to me to have FileMaker behave the way it does, but that might be because I'm a big math geek. It just makes sense to me that when you total numbers, regardless of how they are displayed, you are totalling the numbers as they are stored.

I'm not sure how others feel on this, however, but perhaps if there were enough that would want actual storage to change based on display options, perhaps that could be a preference.

Chuck

[WilliamF]

Chuck:

Thanks for your reply. I have a question. You refer to "Display Options". Maybe I'm missing something here. The only ones I am aware of are (1) General Format (2) As Entered (3) Format a decimal with it's various forms. How can you get a field to show only 2 decimal places unless you use the "Format as a Decimal" function?

That's my problem. I want to show a row of actual dollars and cents totals plus a grand total of these totals. If I use the "Format as a Decimal" <using dollars and cents>, my Grand Total is going to be incorrect compared to the sum of the Totals.

If it's possible (and I don't have a clue) to reprogram FM5 to use only the Rounded totals in it's calcs, then everyone would have some choices. If you wanted to use raw data, then don't Round at all or Round to a large number of places. If you want to Round to (2), then you could do that. I understand that the figures given based on the raw data are more accurate, but not always practical when attempting to produce reports based upon Rounded to (2) numbers. So the saga continues!

WilliamF

[Chuck]

As far as I know, there are only two options for displaying a number to two decimal points, and trufully, it might be best to use both of them.

The first option only affects what is displayed. This is the Format->Number dialog box, where you can tell FileMaker to display a field to any number of decimal points you like. So a field could have a value of 3.14159, but you could display it as 3, 3.14, 3.1416, etc. However, calculations based on this field will use 3.14159.

The other option is to round the number so that the field is actually holding a number to a certain number of decimal places. Round( 3.14159, 0 ) results in 3. Round( 3.14159, 2 ) results in 3.14. Round( 3.14159, 4 ) results in 3.1416.

But, Round( 3.101, 2 ) results in 3.1, so you would want to combine the round function with the display options so that if this number represents dollars, it would display $3.10.

My practice has been to always round at every calculation that is a dollar (or needs to be rounded to a certain number of decimal places for some other reason) if there is any possibility of the result producing decimals in excess of two. So I always use the round function when I am multiplying a dollar amount by a number with a fractional part (multiplying by a whole number would never result in a problem). If I'm just adding or subtracting dollars, then I don't round, because there is no need to.

So, my series of calculations for getting a total of items in a portal for an invoice might be something like this:

Sub_Total = Sum( Line_Items::Extended_Price ) {no rounding because I'm just adding items}

Tax = Round( Sub_Total * Tax_Rate, 2 ) {rounding because I'm probably multiplying a dollar amount by a fractional number}

Total = Sub_Total + Tax {no rounding because I'm adding two dollar amounts}

From here I could then use the display options to make sure that the displayed numbers always show two decimal places (instead of none or one, if the numbers end up to be rounded to a whole dollar amount or a multiple of ten cents).

It is a trade-off, and there probably isn't an answer that would make everyone perfectly happy. However, there is an answer that will leave everyone with what they need.

Chuck

[BobWeaver]

Rounding problems sometimes crop up because for simplicity we often set things up to calculate differently than we would do it manually say. As an example, my own time billing application used to calculate sales tax on each individual item rather than on the subtotal. It was easier this way, because not all items were taxable, and it wasn't practical to show both a taxable and nontaxable subtotal. For display and printing, I just rounded the final subtotal, tax and grand total. Everything worked fine. Then, this July the government added a second sales tax. When I modified the database to include it, I started getting rounding errors. I had to trace back through everything and see just how everything was calculated. Once I had sorted it sorted out in my head the fix was relatively straightforward.

Moral of story: It's important to understand exactly what is being calculated and in what order.