 Miscalculation by PHP

brokaddr
msg:4428827  11:22 pm on Mar 13, 2012 (gmt 0)  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
msg:4428896  3:32 am on Mar 14, 2012 (gmt 0)  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
msg:4428957  9:19 am on Mar 14, 2012 (gmt 0)  #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
msg:4429016  1:02 pm on Mar 14, 2012 (gmt 0)  [php.net...] Additionally, if you need higher precision there are other options: BC: [php.net...] GMP: [php.net...]

rocknbil
msg:4429075  3:46 pm on Mar 14, 2012 (gmt 0)  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
msg:4429127  5:49 pm on Mar 14, 2012 (gmt 0)  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
msg:4429167  7:03 pm on Mar 14, 2012 (gmt 0)  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 reordered 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 & databaserelated stuff  so I apologize if that's a ridiculous question)

lostdreamer
msg:4429443  10:45 am on Mar 15, 2012 (gmt 0)  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
msg:4429446  10:48 am on Mar 15, 2012 (gmt 0)  ... 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
msg:4429456  12:13 pm on Mar 15, 2012 (gmt 0)  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
msg:4430378  4:57 pm on Mar 17, 2012 (gmt 0)  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
msg:4432410  2:38 am on Mar 23, 2012 (gmt 0)  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).


