Miscalculation by PHP

[brokaddr]

I'm not sure if the miscalculation is my fault, PHP or MySQL's.

Code:

$usa_query = tep_db_query("SELECT count(o.orders_id) as totalrecs, sum(round(ot.value,2)) as usatotal FROM ".TABLE_CANCELLED_ORDERS_TOTAL." as ot LEFT JOIN ".TABLE_CANCELLED_ORDERS." as o on(ot.orders_id=o.orders_id) where o.date_purchased LIKE '".$year."-".$month."%' && (o.orders_status='3' || o.orders_status='2099') && (o.delivery_country='United States' || o.delivery_country='Canada') && o.payment_method LIKE '%Credit%' && ot.class='ot_total'");
 $usapp = tep_db_fetch_array($usa_query);
 $usaamt = $usapp['usatotal']*.025;
 $usatotal = $usapp['totalrecs']*0.30;
 $usa = $usaamt+$usatotal;

I've checked the numbers from the spreadsheet in Excel, and the numbers generated off the site are always off by a few cents - I assume it has something to do with the decimal calculations, but I can't figure out how to circumvent it.

I've tried to add number_format($usa,2); over every mathematical result and I end up with the same (incorrect) amounts.

Any suggestions as to where my formula might be wrong?

[brokaddr]

Found the following post, which seems relative:
[stackoverflow.com...]

However, changing stored dollar amounts (2.9900) for example; isn't possible due to the way the site's software is constructed.

On sum() calls where there isn't additional math involved, the amounts match up perfectly with Excel. The query with additional percentage formulas as I've posted above, have me stumped.

[lostdreamer]

#1: I would never ROUND first, then SUM... you could lose small bits of cents there.

If the amounts are off by a few cents, I'd start looking there.

sum(round(ot.value,2)) as usatotal
becomes:
round(sum(ot.value),2) as usatotal

Let me know ;)

[eelixduppy]

[php.net...]

Additionally, if you need higher precision there are other options:

BC: [php.net...]
GMP: [php.net...]

[rocknbil]

numbers generated off the site are always off by a few cents

You're probably seeing the effects of floating point precision, which not only affects PHP, it affects just about every programming language and is machine specific. Every processor has a floating point precision placeholder*
25.00000000000001

and has different decimal places for different for different machines. That placeholder throws off the decimal calculations. It used to drive me nuts. I usually do

sprintf("%.2f",intval($raw_num * 100)/100));

... because rounding rounds down, ceiling functions round up, the above usually gets it right, and is identical in perl and php. In Javascript it's num.toFixed(2) instead of sprintf().

*The precise terms and lanaguage is probably inaccurate, but that's the gist.

[Demaestro]

Something I have learned over the years is that is it easiest to store the smallest possible unit and then convert up.

So in this case storing pennies instead of dollars would help because you aren't dealing with decimals.

150 cents is easier to deal with than 1.5 dollars, especially when performing math operations using SQL. I am not sure how far long your application is but you should consider this an option.

[brokaddr]

I sincerely appreciate the replies! I've spent the better part of the past several days racking my brain and researching why this was happening.

After seeing your guys' posts & that this is a known issue, I figured out how to import these fees into my db, rather than calculate on the fly. (No longer 2.9900, but 2.99 now) - unfortunately, I still ran into the same miscalculation issue!

Then I took lostdreamer's advice and re-ordered my sum() and round() - the numbers are now exact. :)


On a related note, does number_format($var,2) have the same "float" issue, or is this strictly a way to add commas and decimals into the correct spaces?
(I'm extremely new to php currency/math calculations; until now I've only ever done typical scripting & database-related stuff - so I apologize if that's a ridiculous question)

[lostdreamer]

AFAIK everything with decimals has a "floating point" and every language has a "floating point issue"

number_format is just to make sure that comma's and dots are in the right place so PHP doesnt think that 2,900 is 2900 instead of 2.9 (or the other way around).

[lostdreamer]

... because rounding rounds down, ceiling functions round up...

Actually, ROUND() goes up and down (0.5 => 1, 0.49 => 0)
CEIL only goes up
FLOOR only goes down

[PCInk]

To put it simply why this is happening. Computers cannot store 0.1 or 0.01 as decimal fractions accurately. Imagine you are adding three numbers together in our normal decimal system using fractions and decimals:

1) One third + One third + One third = 1
2) Replace one third with decimals, this is 0.333333 rounded to six decimal places
3) 0.333333 + 0.333333 + 0.333333 = 0.999999
4) Notice, no matter how many 3's you round to, it will never actually equal one, even those it is close!
5) The more calculations you do with these rounded numbers, the greater the error of accuracy

Your computer is doing to same thing with numbers that it cannot store such as "0.01". As suggested earlier, always store currency in pennies/cents converting to dollars/pounds/euros as you print the item to the screen.

To solve your issue here, you could try to convert the numbers to pennies before any mathematical calculation. So use int(ot.value*100) - integer to get rid rogue conversion fractions. See if this works, though the output will be in cents/pennies. Then, from the cents/pennies, you can convert to dollars/pounds after all the calculations have been done.

[enigma1]

You should be using the tep_round function after your calculations and specify 2 decimals for the rounding. If you search the function in your files it is used extensively.

[lexipixel]

To keep the rounding errors consistent and manageable, I try replace all division with multiplication, e.g.-

Instead of:

100 / 3 = 33.333...

I'd Use:

100 * .3333 = 33.33

It's not perfect, but you can control (or at least know what to expect) from the results.

(FOOTNOTE: This is why grocery stores sell things "3 For $2.00"... instead of trying to figure out exactly how much one costs, they know you'll take the mentally lazy route and just buy three).