If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Incorrect result for simple SUM formula
Hello,
Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V |
#2
|
|||
|
|||
Incorrect result for simple SUM formula
What is the values you are having in D12 to D22?
-------------------- (Ms-Exl-Learner) -------------------- "VivienW" wrote: Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V |
#3
|
|||
|
|||
Incorrect result for simple SUM formula
The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12)
It calculates the price of items sold as a unit or by weight. The final cell containing the error totals the individual items. -- Cheers, V "Ms-Exl-Learner" wrote: What is the values you are having in D12 to D22? -------------------- (Ms-Exl-Learner) -------------------- "VivienW" wrote: Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V |
#4
|
|||
|
|||
Incorrect result for simple SUM formula
And what were the extra decimal places in D12 to D22 inclusive?
If you want to round the contents of each of the cells D12 to D22 to 2 decimal places and then add the results, use =SUM(ROUND(B12:B22,2)) and enter as an array formula (Control Shift Enter). -- David Biddulph "VivienW" wrote in message ... Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V |
#5
|
|||
|
|||
Incorrect result for simple SUM formula
Yes. So how many decimal places does the result of that calculation have?
[And I don't mean how many decimal places have you currently formatted to *display*, but how many decimal places are there in the number *stored* in the cell, because it is those stored numbers that you are adding.] See my reply elsewhere in this thread. -- David Biddulph "VivienW" wrote in message ... The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12) It calculates the price of items sold as a unit or by weight. The final cell containing the error totals the individual items. -- Cheers, V "Ms-Exl-Learner" wrote: What is the values you are having in D12 to D22? -------------------- (Ms-Exl-Learner) -------------------- "VivienW" wrote: Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V |
#6
|
|||
|
|||
Incorrect result for simple SUM formula
Thanks David,
I was really only hoping for a reason Excel does not always give the correct result. I am trying to replicate the file but it won't give the wrong answer as I'm not using the same figures to start with, so am unable to tell you how many decimal places are involved. I will try out the formula you suggest. Thanks again. -- Cheers, V "David Biddulph" wrote: And what were the extra decimal places in D12 to D22 inclusive? If you want to round the contents of each of the cells D12 to D22 to 2 decimal places and then add the results, use =SUM(ROUND(B12:B22,2)) and enter as an array formula (Control Shift Enter). -- David Biddulph "VivienW" wrote in message ... Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V . |
#7
|
|||
|
|||
Incorrect result for simple SUM formula
I think you'll find that Excel *is* giving the correct result (subject to
its limitation to 15 significant figures, and that wouldn't give the sort of discrepancy which your figures describe), but it will give the result to the question you asked it, not necessarily the question that you might have intended to ask. In general, if you find that the answer isn't what you expected, it's always worth checking the data values which are being fed into the formula. -- David Biddulph "VivienW" wrote in message ... Thanks David, I was really only hoping for a reason Excel does not always give the correct result. I am trying to replicate the file but it won't give the wrong answer as I'm not using the same figures to start with, so am unable to tell you how many decimal places are involved. I will try out the formula you suggest. Thanks again. -- Cheers, V "David Biddulph" wrote: And what were the extra decimal places in D12 to D22 inclusive? If you want to round the contents of each of the cells D12 to D22 to 2 decimal places and then add the results, use =SUM(ROUND(B12:B22,2)) and enter as an array formula (Control Shift Enter). -- David Biddulph "VivienW" wrote in message ... Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V . |
#8
|
|||
|
|||
Incorrect result for simple SUM formula
"VivienW" wrote in several postings:
1 e.g. =SUM(D1222) result 13.75 (should be 13.76) increase 1 decimal places result becomes 13.74643 2 The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12) 2 It calculates the price of items sold as a unit or by weight. 3 I was really only hoping for a reason Excel does not always 3 give the correct result. One likely explanation: the results in some or all of D1222 [sic] are not accurate to just 2 decimal places. For example, if A12 is zero, B12 is 123 and D12 is 0.125, the result of B12*D12 might be displayed as 15.38 due to formatting, but it is really 15.375. Temporarily, format D1222 to 5 decimal places to confirm that that is the problem. Since that formula is intended to compute price, it would be better to write: =ROUND(IF(A12=0, B12*D12, A12*D12), 2) Nonetheless, it would also be wise to write: =ROUND(SUM(D1222), 2) (Note: There seems to be a circular reference in your examples. You are summing D1222, but you say the formula in each cell is, e.g, IF(A12=0,B12*D12,A12*D12) presumably in D12. I suspect there has been a change in the spreadsheet that caused a renaming of cells. Presumably, that is not germane to the problem.) That avoids another possible problem with arithmetic applied to numbers with decimal fractions. To illustrate, try the following: =IF(10.1 - 0.1 = 0.1, TRUE) That results in FALSE(!). Again, this problem is avoided by the prudent use of ROUND, in this case: =IF(ROUND(10.1 - 0.1, 1) = 0.1, TRUE) That example demonstrates another possible explanation of your problem with SUM: the way that Excel (and most applications) store and perform arithmetic on numbers with decimal fractions, namely "binary floating point". I believe that is not likely to be your primary problem because of the magnitude of the unexpected result of SUM, namely 13.74643 instead of 13.75. But it might be a contributing factor. Again, the remedy to both problems is the same: the pervasive, but prudent use of ROUND. Although formatting can be used to round the __displayed__ value, formatting does not change the underlying __actual__ value. ----- original message ----- "VivienW" wrote in message ... Thanks David, I was really only hoping for a reason Excel does not always give the correct result. I am trying to replicate the file but it won't give the wrong answer as I'm not using the same figures to start with, so am unable to tell you how many decimal places are involved. I will try out the formula you suggest. Thanks again. -- Cheers, V "David Biddulph" wrote: And what were the extra decimal places in D12 to D22 inclusive? If you want to round the contents of each of the cells D12 to D22 to 2 decimal places and then add the results, use =SUM(ROUND(B12:B22,2)) and enter as an array formula (Control Shift Enter). -- David Biddulph "VivienW" wrote in message ... Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V ----- another message ---- "VivienW" wrote in message ... The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12) It calculates the price of items sold as a unit or by weight. The final cell containing the error totals the individual items. -- Cheers, V |
#9
|
|||
|
|||
Incorrect result for simple SUM formula
On Fri, 20 Nov 2009 02:20:02 -0800, VivienW
wrote: Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 It is very unlikely that the result in Excel is incorrect. What is more likely is that the *actual* values in D1222 add up to 13.74643. You are probably making the common error of assuming that what is being displayed in D1222 alters the precision of what is being *stored* in D1222. If you expand the display of D1222 to, let us say, 5+ decimals, you will probably determine that Excel is adding correctly. If you are only interested in two decimal precision, then you should add a Rounding function. e.g. =round(IF(A12=0,B12*D12,A12*D12),2) --ron |
#10
|
|||
|
|||
Incorrect result for simple SUM formula
Errata....
I wrote: =IF(10.1 - 0.1 = 0.1, TRUE) Of course, that should be: =IF(10.1 - 10 = 0.1, TRUE) ----- original message ----- "Joe User" joeu2004 wrote in message ... "VivienW" wrote in several postings: 1 e.g. =SUM(D1222) result 13.75 (should be 13.76) increase 1 decimal places result becomes 13.74643 2 The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12) 2 It calculates the price of items sold as a unit or by weight. 3 I was really only hoping for a reason Excel does not always 3 give the correct result. One likely explanation: the results in some or all of D1222 [sic] are not accurate to just 2 decimal places. For example, if A12 is zero, B12 is 123 and D12 is 0.125, the result of B12*D12 might be displayed as 15.38 due to formatting, but it is really 15.375. Temporarily, format D1222 to 5 decimal places to confirm that that is the problem. Since that formula is intended to compute price, it would be better to write: =ROUND(IF(A12=0, B12*D12, A12*D12), 2) Nonetheless, it would also be wise to write: =ROUND(SUM(D1222), 2) (Note: There seems to be a circular reference in your examples. You are summing D1222, but you say the formula in each cell is, e.g, IF(A12=0,B12*D12,A12*D12) presumably in D12. I suspect there has been a change in the spreadsheet that caused a renaming of cells. Presumably, that is not germane to the problem.) That avoids another possible problem with arithmetic applied to numbers with decimal fractions. To illustrate, try the following: =IF(10.1 - 0.1 = 0.1, TRUE) That results in FALSE(!). Again, this problem is avoided by the prudent use of ROUND, in this case: =IF(ROUND(10.1 - 0.1, 1) = 0.1, TRUE) That example demonstrates another possible explanation of your problem with SUM: the way that Excel (and most applications) store and perform arithmetic on numbers with decimal fractions, namely "binary floating point". I believe that is not likely to be your primary problem because of the magnitude of the unexpected result of SUM, namely 13.74643 instead of 13.75. But it might be a contributing factor. Again, the remedy to both problems is the same: the pervasive, but prudent use of ROUND. Although formatting can be used to round the __displayed__ value, formatting does not change the underlying __actual__ value. ----- original message ----- "VivienW" wrote in message ... Thanks David, I was really only hoping for a reason Excel does not always give the correct result. I am trying to replicate the file but it won't give the wrong answer as I'm not using the same figures to start with, so am unable to tell you how many decimal places are involved. I will try out the formula you suggest. Thanks again. -- Cheers, V "David Biddulph" wrote: And what were the extra decimal places in D12 to D22 inclusive? If you want to round the contents of each of the cells D12 to D22 to 2 decimal places and then add the results, use =SUM(ROUND(B12:B22,2)) and enter as an array formula (Control Shift Enter). -- David Biddulph "VivienW" wrote in message ... Hello, Does anyone know why Excel sometimes give an incorrect result when adding the contents of cells? It may only be 1p out but even if I increase the decimal places it doesn't seem to make sense as the decimal places are showing a lesser number than the +1p error. e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places result becomes 13.74643 -- Cheers, V ----- another message ---- "VivienW" wrote in message ... The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12) It calculates the price of items sold as a unit or by weight. The final cell containing the error totals the individual items. -- Cheers, V |
Thread Tools | |
Display Modes | |
|
|