Forum Discussion

EExmannhotmailcom's avatar
EExmannhotmailcom
Copper Contributor
Jul 05, 2024

Calculation error

Hello,

I have a matrix of numeric values 13 by 7 cells.

If I summarize the horizontal figures (13 columns in total) it gives me a total.

If I do the same (different purpose) for the vertical (7 rows in total) it also gives me a total.

No formatting. except 2 digits behind comma.

I thought the total of the 13 columns would be the same as the total of the 7 rows.

I learned that Excel calculates through 15 digits behind the comma.

But I find that the total of the 7 rows it calculates to 14 digits behind the comma, the 15th digit is forgotten(?). The total of the 13 columns also loses its 15th digit behind the comma.

That would make is even, but no horizontal total of columns is NOT EQUAL TO vertical total of rows.

Please explain or help to get it accurate.

 

On request, I can supply the matrix to describe my calculation error further.

 

Kind regards Eddy  

 

  • EExmannhotmailcom 

    Hello Joe User,

     

    Thanks for your reply's, don't know why I received your request (13 times) for concrete examples.

     

    You clarified one thing for me,

    "The actual limitation is the internal binary precision, not a decimal precision.

    Second, the formatting limit is 15 significant digits, not decimal places.

    For example, 12345.6789012345 has only 10 decimal places, but it has 15 significant digits, just as 0.123456789012345 does."

     

    That is the point where my calculations go wrong, but since I added a check, which notify’ s me if there is a difference in the total horizontal compared to the total vertical. It makes it easier for me to rule out any problems.

    I have added the example you requested. Hope this clarifies it for you.

  • EExmannhotmailcom 

    Because of the way Excel stores and processes numbers, the order of calculations can cause tiny differences.

    To work around this, use the ROUND function to round the totals to a 'reasonable' number of decimal places.

    For example, if the numbers in the matrix all have up to 2 decimal places, use

    =ROUND(SUM(...), 2)

    That way, the row total and column total will be equal.

    • EExmannhotmailcom's avatar
      EExmannhotmailcom
      Copper Contributor

      HansVogelaar

      HI Hans, yes that is possible, but off the approx 2366 figures only 2 batches matrix of 182 figures total (2 times 13columns-7rows) don't add up. Don't want to round, because with such large amount of figures (still growing) I would filter the abnormale figures. 

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        EExmannhotmailcom 

        If the numbers in the matrix have at most n decimal places, their sum by row or column will also have at most n decimal places, so it makes sense to round to n decimal places. If you want, you can round to n+1 or n+2 decimal places to find discrepancies. But you cannot expect calculations to be correct to 15 decimal places.

  • EExmannhotmailcom 

    Hello Joe User,

     

    Thanks for your reply's, don't know why I received your request (13 times) for concrete examples.

     

    You clarified one thing for me,

    "The actual limitation is the internal binary precision, not a decimal precision.

    Second, the formatting limit is 15 significant digits, not decimal places.

    For example, 12345.6789012345 has only 10 decimal places, but it has 15 significant digits, just as 0.123456789012345 does."

     

    That is the point where my calculations go wrong, but since I added a check, which notify’ s me if there is a difference in the total horizontal compared to the total vertical. It makes it easier for me to rule out any problems.

    I have added the example you requested. Hope this clarifies it for you.

    • JoeUser2004's avatar
      JoeUser2004
      Bronze Contributor

      EExmannhotmailcom 

       

      Ignore my previous response to your latest follow-up comments.  I am rushed, and my response was ill-considered.

       

      I will reply later -- unless by marking your follow-up comments as "best", your intention is to end the discussion.

      • EExmannhotmailcom's avatar
        EExmannhotmailcom
        Copper Contributor
        Hi Joe,
        Don't know how to attach the excel file. But your explanation of digits and decimale is sufficient to me. I only wish I could sent the file but you can close the case. Thanks for your reply kind regards eddy
  •  

     Totalen Gas Verbruik m3 
    Datum vanTemp.- / Energie13141516171819202122232425 Temp.- / EnergieDag TotaalTotaal WerkweekTotaal Weekend 
    15-4-2024MA0,00,00,00,01,32,00,40,30,40,00,00,00,0 MA4,44   
    16-4-2024DI0,00,00,00,01,82,30,10,52,70,00,00,00,0 DI7,40   
    17-4-2024WO0,00,00,00,01,41,61,00,61,80,00,00,00,0 WO6,50   
    18-4-2024DO0,00,00,00,01,81,12,10,82,80,00,00,00,0 DO8,56   
    19-4-2024VR0,00,00,00,01,70,61,80,52,80,00,00,00,0 VR7,4334,33  
    20-4-2024ZA0,00,00,00,01,50,02,11,52,80,00,00,00,0 ZA7,91   
    21-4-2024ZO0,00,00,00,01,50,02,51,83,00,00,00,00,0 ZO8,89 16,79 
    Datum t/mTotaal0,00,00,00,011,17,610,16,116,30,00,00,00,0 Totaal51,120,000000000000007 
     Uren0,00,00,00,056,028,229,517,037,30,00,00,00,0 Uren    
     m3/uur0,000,000,000,000,200,270,340,360,440,000,000,000,00 m3/uur1,60   
                         
                         
                         
     Totalen Gas Verbruik m3 
    Datum vanTemp.- / Energie13141516171819202122232425 Temp.- / EnergieDag TotaalTotaal WerkweekTotaal Weekend 
    20-5-2024MA0,00,00,00,00,50,70,00,10,00,00,00,00,0 MA1,32   
    21-5-2024DI0,00,00,00,00,40,30,00,00,00,00,00,00,0 DI0,76   
    22-5-2024WO0,00,00,00,00,00,60,00,00,00,00,00,00,0 WO0,70   
    23-5-2024DO0,00,00,00,00,40,40,00,00,00,00,00,00,0 DO0,89   
    24-5-2024VR0,00,00,00,00,00,50,00,00,00,00,00,00,0 VR0,504,16  
    25-5-2024ZA0,00,00,00,00,50,20,00,00,00,00,00,00,0 ZA0,76   
    26-5-2024ZO0,00,00,00,00,40,10,20,00,00,00,00,00,0 ZO0,71 1,47 
    Datum t/mTotaal0,00,00,00,02,43,00,20,10,00,00,00,00,0 Totaal5,63-0,000000000000001 
     Uren0,00,00,00,046,8116,22,52,50,00,00,00,00,0 Uren    
     m3/uur0,000,000,000,000,050,030,090,040,000,000,000,000,00 m3/uur0,20   
                         

Resources