SOLVED

Calculation error

Copper Contributor

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  

 

11 Replies

@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.

@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. 

@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.

@HansVogelaar Screen shot of the test Matrices:

EExmannhotmailcom_0-1720362313454.png

Hope you can enlarge it for reading purpose.

Eddy

 

@EExmannhotmailcom 

I'd use scientific notation for such numbers.

best response confirmed by EExmannhotmailcom (Copper Contributor)
Solution

@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.

 

 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   
                     

@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.

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

@EExmannhotmailcom  wrote: "your explanation of digits and decimale is sufficient to me. [.... So] you can close the case"

 

But you have some much bigger mathematical problems and some dubious decimal presentation issues that have nothing to do with the binary arithmetic issue that Hans and I addressed.

 

(The following assumes that the upper-left corner of the first table is A1.)

 

-----

 

1. Your calculation of "total" m3/hour is incorrect.  Mathematically, it should be the weighted average of the daily m3/hour amounts, not merely their sum.  Ostensibly:

 

=SUMPRODUCT(C12:O12, C11:O11) / SUM(C11:O11)

 

Alternatively:

 

=SUM(C10:O10) / SUM(C11:O11)

 

The two formulas return the same result (subject to binary arithmetic anomalies), namely 0.304761904761905, if your actual daily m3/hour values in C12:O12 are their exact daily ratios, for example G10/G11.

 

(The SUMPRODUCT result is slightly different with the rounded values that you posted.)

 

-----

 

2. Apparently, the "daily" totals in R3:R9 and the weekly totals in C10:O10 are based on actual values with more decimal precision than the values that you posted in C3:O9.

 

For example, based on the posted values in C3:O9, =SUM(C3:O3) is 4.40 instead of 4.44 that you posted in R3.  And =SUM(I3:I9) is 10.00 instead of 10.10 that you posted in I10.

 

Consequently, we cannot duplicate the visable difference (0.000000000000007) between your total in R10, presumably =SUM(C10:O10), and the sum of the "daily" totals, presumably =SUM(R3:R9).

 

(In the future, please post your formulas as well as their results, at least.)

 

Nevertheless, there is an invisible difference between those sums based on the posted values.

 

Both sums appear to be 50.9000000000000.  But with formulas like =SUM(C3:O3) in V3:V9 and =SUM(C3:C9) in C14:O14, the results of =SUM(V3:V9) in V10 and =SUM(C14:O14) in P14 actually differ by about 1.42E-14 (**).

The reason is not so much because the order of operations is different, but because the values are different.

 

Nevertheless, for either reason, the explanation is the same:  the binary representation of most decimal fractions is not exact, and the binary approximation can vary depending on the magnitude of the value.  Consequently, the binary arithmetic result can differ infinitesimally from the expected decimal result.

 

For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!) because the binary approximation of 0.1 in 10.1 differs from the binary approximation of 0.1 by itself. 

 

In P14, the sum is 11.00 + 7.60 + 10.00 + 6.00 + 16.30.  The 17-digit (*) approximation is 50.900000000000006.

 

In V10, the sum is  4.40 + 7.40 + 6.40 + 8.60 + 7.40 + 7.90 + 8.80.  The 17-digit approximation is 50.899999999999991.

 

Aside.... The following is an example where the order of operations makes a difference (compare with P14):  11.00 + 10.00 + 6.00 + 16.30 + 7.60 results in the 17-digit approximation 50.899999999999999.

 

(By coincidence, that is also the exact decimal representation of the binary approximation of the constant 50.9.)

 

-----

TMI....

 

(*) Excel does not format the 17-digit approximation except in XML files, like the internal represenation of xlsx and xlsm files, and when transferring values between some applications, like MS Access.  17 significant digits is necessary and sufficient to replicate the binary approximation.

 

(**) The difference of 1.42E-14 is invisible if we calculate the formula =P14-V10 (might return exactly zero artificially) or the conditional expression P14=V10 (returns TRUE).  That is due to dubious design "features" that are unique to Excel.

 

In contrast, =(P14-V10) with redundant parentheses does display 1.42E-14 when formatted as General or Scientific.  And both ISNUMBER(MATCH(P14, V10, 0)) and P14 - V10 = 0 return FALSE because of the actual infinitesimal difference.

Hi Joe and or Hans,
I greatly respect your effort. I will be abroad for a few days. But wil definitly come back to you next week after I have given your input some consideration. For now thank you very much. I appreciate it very much. Kind regards, Eddy
1 best response

Accepted Solutions
best response confirmed by EExmannhotmailcom (Copper Contributor)
Solution

@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.

View solution in original post