Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
I have tables of figures which I am extracting, via VLOOKUP, to different
reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
Your mis-match may be a tiny bit of rounding error. This can be detected by
comparing the two values with an IF statement. It can be fixed by using the ROUND() function, -- Gary''s Student - gsnu200855 "BabyMc" wrote: I have tables of figures which I am extracting, via VLOOKUP, to different reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
Thanks Gary's Student
I'm not sure I'm with you exactly. Every value in the table is to 2 decimal places only and my report is only taking those values and adding or subtracting (no multplication or division) - so I couldn't see how either could have any rounding error. In addition I did try putting =ROUND( ,2) round my lookups but that didn't make any difference. Would you be able to expplain further what you mean? Thanks again "Gary''s Student" wrote: Your mis-match may be a tiny bit of rounding error. This can be detected by comparing the two values with an IF statement. It can be fixed by using the ROUND() function, -- Gary''s Student - gsnu200855 "BabyMc" wrote: I have tables of figures which I am extracting, via VLOOKUP, to different reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
Sometimes people FORMAT a cell to only display 2 digits. The REAL underlying
value ccan still have a tiny bit of roundoff error. ROUND should be applied to the calculated values before trying lookups. -- Gary''s Student - gsnu200855 "BabyMc" wrote: Thanks Gary's Student I'm not sure I'm with you exactly. Every value in the table is to 2 decimal places only and my report is only taking those values and adding or subtracting (no multplication or division) - so I couldn't see how either could have any rounding error. In addition I did try putting =ROUND( ,2) round my lookups but that didn't make any difference. Would you be able to expplain further what you mean? Thanks again "Gary''s Student" wrote: Your mis-match may be a tiny bit of rounding error. This can be detected by comparing the two values with an IF statement. It can be fixed by using the ROUND() function, -- Gary''s Student - gsnu200855 "BabyMc" wrote: I have tables of figures which I am extracting, via VLOOKUP, to different reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
I don't understand why one would need to put the round function around a
lookup where the results are not to more decimal places than required. However, having said that, inserting the round function around the lookup does cure it. This would leave me with another question. How does one easily insert the round function around the lookup - when I have many forumlas to do and the lookup is using many different filenames (which, I think, prevents me from using edit replace)? Thanks again "Gary''s Student" wrote: Sometimes people FORMAT a cell to only display 2 digits. The REAL underlying value ccan still have a tiny bit of roundoff error. ROUND should be applied to the calculated values before trying lookups. -- Gary''s Student - gsnu200855 "BabyMc" wrote: Thanks Gary's Student I'm not sure I'm with you exactly. Every value in the table is to 2 decimal places only and my report is only taking those values and adding or subtracting (no multplication or division) - so I couldn't see how either could have any rounding error. In addition I did try putting =ROUND( ,2) round my lookups but that didn't make any difference. Would you be able to expplain further what you mean? Thanks again "Gary''s Student" wrote: Your mis-match may be a tiny bit of rounding error. This can be detected by comparing the two values with an IF statement. It can be fixed by using the ROUND() function, -- Gary''s Student - gsnu200855 "BabyMc" wrote: I have tables of figures which I am extracting, via VLOOKUP, to different reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
One way is to attack the problem at the point of calculation. So instead of:
=SUM(A1:A100) use: =ROUND(SUM(A1:A100),2) At least you understand that the problem is not YOU, it is how Excel performs calculations. -- Gary''s Student - gsnu200855 "BabyMc" wrote: I don't understand why one would need to put the round function around a lookup where the results are not to more decimal places than required. However, having said that, inserting the round function around the lookup does cure it. This would leave me with another question. How does one easily insert the round function around the lookup - when I have many forumlas to do and the lookup is using many different filenames (which, I think, prevents me from using edit replace)? Thanks again "Gary''s Student" wrote: Sometimes people FORMAT a cell to only display 2 digits. The REAL underlying value ccan still have a tiny bit of roundoff error. ROUND should be applied to the calculated values before trying lookups. -- Gary''s Student - gsnu200855 "BabyMc" wrote: Thanks Gary's Student I'm not sure I'm with you exactly. Every value in the table is to 2 decimal places only and my report is only taking those values and adding or subtracting (no multplication or division) - so I couldn't see how either could have any rounding error. In addition I did try putting =ROUND( ,2) round my lookups but that didn't make any difference. Would you be able to expplain further what you mean? Thanks again "Gary''s Student" wrote: Your mis-match may be a tiny bit of rounding error. This can be detected by comparing the two values with an IF statement. It can be fixed by using the ROUND() function, -- Gary''s Student - gsnu200855 "BabyMc" wrote: I have tables of figures which I am extracting, via VLOOKUP, to different reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
"BabyMc" wrote:
How does one easily insert the round function around the lookup - when I have many forumlas to do and the lookup is using many different filenames (which, I think, prevents me from using edit replace)? As JLatham suggested, at this point, your "easiest" solution might be to set the calculation option "Precision as displayed". In Excel 2003, click on Tools Options Calculation. But do be careful. That option affects all cells in the workbook that have a non-General numeric format, and the effect is non-reversible for such cells with constants. For example, if you have the constant 0.125 in a cell formatted as Number with 2 decimal places, setting PAD will change the constant to 0.13, which will affect calculations in other dependent cells. Moreover, note that PAD does not affect calculations in cells formatted as General, the default. So PAD might seem to have no or less benefit sometimes. The alternative, using ROUND prolifically, is better, in my opinion. But in order to do add this after the fact, the "easiest" solution might require writing a macro. The macro itself may be non-trivial to design, depending on how robust it needs to be. And if you are unfamiliar with macros, that approach might be infeasible. I don't understand why one would need to put the round function around a lookup where the results are not to more decimal places than required. Perhaps the following examples and explanation will help. Suppose A1 contains 10.7, A2 contains =A1-10, and A3 contains 0.7. If you do a comparison of A2 and A3, or look up A3 in a table containing A2, they will not match exactly. In this case, A2 is slightly less than A3, so a typical VLOOKUP might still work. But in other examples, A2 might be slightly larger than A3. In contrast, suppose A1 contains 1.7, and A2 contains =A1-1. In this case, a comparison of A2 and A3 is an exact match. Why the difference? Most decimal fractions cannot be represented exactly in the internal form that Excel uses to represent numbers -- a standard binary floating-pointing form. We are encountering different approximations for 0.7. 10.7 is represented internally as 10.6999999999999,992894572642398998141288757324218 75. (The comma is my way of demarcating 15 significant digits to the left.) When we subtract 10, we get .699999999999999,289457264239899814128875732421875 . But 0.7 is represented internally as 0.699999999999999,95559107901499373838305473327636 71875. You can see that they differ starting in the 16th significant digit, and the first representation is indeed less than the second representation. So, why do we get two different approximations of 0.7? In a nutshell, because numbers are represented internally by 53 consecutive powers of 2 ("bits"). With 0.7, we can use the full 53 bits to represent 0.7. But with 10.7, some of the bits are used to represent 10; so there are fewer bits to represent 0.7. In this case, that results in a less accurate representation of 0.7. When we subtract 10, we are left with the less accurate representation of 0.7. In contrast, the internal representation of 1.7 has the same approximation of 0.7 as 0.7 itself because fewer bits are needed to represent 1. That is, 1.7 is represented internally as 1.69999999999999,995559107901499373838305473327636 71875. So when we subtract 1, we get the same respresentation of 0.7. All of this seems mysterious and difficult to predict, for a number of complicated reasons. Besides the anomaly demonstrated above, Excel employs some heuristics (algorithms) to try to hide such differences by "correcting" the result of some calculations. But the heuristics are implemented inconsistently, sometimes resulting in even more mysterious behavior. For example, using different numbers, the expression IF(A2A1,A2-A1,0) might result in a negative result very close to zero (e.g. about -2E-16). I hope this helps. In my opinion, the best solution is to be aware of this ever-present problem with arithmetic involving numbers with decimal fractions and, with that foresight, to use ROUND prolifically from the start. ----- original message ----- "BabyMc" wrote in message ... I don't understand why one would need to put the round function around a lookup where the results are not to more decimal places than required. However, having said that, inserting the round function around the lookup does cure it. This would leave me with another question. How does one easily insert the round function around the lookup - when I have many forumlas to do and the lookup is using many different filenames (which, I think, prevents me from using edit replace)? Thanks again "Gary''s Student" wrote: Sometimes people FORMAT a cell to only display 2 digits. The REAL underlying value ccan still have a tiny bit of roundoff error. ROUND should be applied to the calculated values before trying lookups. -- Gary''s Student - gsnu200855 "BabyMc" wrote: Thanks Gary's Student I'm not sure I'm with you exactly. Every value in the table is to 2 decimal places only and my report is only taking those values and adding or subtracting (no multplication or division) - so I couldn't see how either could have any rounding error. In addition I did try putting =ROUND( ,2) round my lookups but that didn't make any difference. Would you be able to expplain further what you mean? Thanks again "Gary''s Student" wrote: Your mis-match may be a tiny bit of rounding error. This can be detected by comparing the two values with an IF statement. It can be fixed by using the ROUND() function, -- Gary''s Student - gsnu200855 "BabyMc" wrote: I have tables of figures which I am extracting, via VLOOKUP, to different reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Balanced formula does not balance
Thanks (both JoeU2004 and JLatham)
I'm not sure I want to go down the PAD route in this instance - though I shall try and remember it for future reference. I'm not familiar, enough with VB, so I think I will go with Edit, Replace. For what I need to do, and given that I will need to access each workbook anyway it will be my best option. Thanks again "JoeU2004" wrote: "BabyMc" wrote: How does one easily insert the round function around the lookup - when I have many forumlas to do and the lookup is using many different filenames (which, I think, prevents me from using edit replace)? As JLatham suggested, at this point, your "easiest" solution might be to set the calculation option "Precision as displayed". In Excel 2003, click on Tools Options Calculation. But do be careful. That option affects all cells in the workbook that have a non-General numeric format, and the effect is non-reversible for such cells with constants. For example, if you have the constant 0.125 in a cell formatted as Number with 2 decimal places, setting PAD will change the constant to 0.13, which will affect calculations in other dependent cells. Moreover, note that PAD does not affect calculations in cells formatted as General, the default. So PAD might seem to have no or less benefit sometimes. The alternative, using ROUND prolifically, is better, in my opinion. But in order to do add this after the fact, the "easiest" solution might require writing a macro. The macro itself may be non-trivial to design, depending on how robust it needs to be. And if you are unfamiliar with macros, that approach might be infeasible. I don't understand why one would need to put the round function around a lookup where the results are not to more decimal places than required. Perhaps the following examples and explanation will help. Suppose A1 contains 10.7, A2 contains =A1-10, and A3 contains 0.7. If you do a comparison of A2 and A3, or look up A3 in a table containing A2, they will not match exactly. In this case, A2 is slightly less than A3, so a typical VLOOKUP might still work. But in other examples, A2 might be slightly larger than A3. In contrast, suppose A1 contains 1.7, and A2 contains =A1-1. In this case, a comparison of A2 and A3 is an exact match. Why the difference? Most decimal fractions cannot be represented exactly in the internal form that Excel uses to represent numbers -- a standard binary floating-pointing form. We are encountering different approximations for 0.7. 10.7 is represented internally as 10.6999999999999,992894572642398998141288757324218 75. (The comma is my way of demarcating 15 significant digits to the left.) When we subtract 10, we get .699999999999999,289457264239899814128875732421875 . But 0.7 is represented internally as 0.699999999999999,95559107901499373838305473327636 71875. You can see that they differ starting in the 16th significant digit, and the first representation is indeed less than the second representation. So, why do we get two different approximations of 0.7? In a nutshell, because numbers are represented internally by 53 consecutive powers of 2 ("bits"). With 0.7, we can use the full 53 bits to represent 0.7. But with 10.7, some of the bits are used to represent 10; so there are fewer bits to represent 0.7. In this case, that results in a less accurate representation of 0.7. When we subtract 10, we are left with the less accurate representation of 0.7. In contrast, the internal representation of 1.7 has the same approximation of 0.7 as 0.7 itself because fewer bits are needed to represent 1. That is, 1.7 is represented internally as 1.69999999999999,995559107901499373838305473327636 71875. So when we subtract 1, we get the same respresentation of 0.7. All of this seems mysterious and difficult to predict, for a number of complicated reasons. Besides the anomaly demonstrated above, Excel employs some heuristics (algorithms) to try to hide such differences by "correcting" the result of some calculations. But the heuristics are implemented inconsistently, sometimes resulting in even more mysterious behavior. For example, using different numbers, the expression IF(A2A1,A2-A1,0) might result in a negative result very close to zero (e.g. about -2E-16). I hope this helps. In my opinion, the best solution is to be aware of this ever-present problem with arithmetic involving numbers with decimal fractions and, with that foresight, to use ROUND prolifically from the start. ----- original message ----- "BabyMc" wrote in message ... I don't understand why one would need to put the round function around a lookup where the results are not to more decimal places than required. However, having said that, inserting the round function around the lookup does cure it. This would leave me with another question. How does one easily insert the round function around the lookup - when I have many forumlas to do and the lookup is using many different filenames (which, I think, prevents me from using edit replace)? Thanks again "Gary''s Student" wrote: Sometimes people FORMAT a cell to only display 2 digits. The REAL underlying value ccan still have a tiny bit of roundoff error. ROUND should be applied to the calculated values before trying lookups. -- Gary''s Student - gsnu200855 "BabyMc" wrote: Thanks Gary's Student I'm not sure I'm with you exactly. Every value in the table is to 2 decimal places only and my report is only taking those values and adding or subtracting (no multplication or division) - so I couldn't see how either could have any rounding error. In addition I did try putting =ROUND( ,2) round my lookups but that didn't make any difference. Would you be able to expplain further what you mean? Thanks again "Gary''s Student" wrote: Your mis-match may be a tiny bit of rounding error. This can be detected by comparing the two values with an IF statement. It can be fixed by using the ROUND() function, -- Gary''s Student - gsnu200855 "BabyMc" wrote: I have tables of figures which I am extracting, via VLOOKUP, to different reports. I am then compiling a total, of each of these values, in the reports themselves. Then I am comparing the total given in my report with the total that is also included within the initial table and asking Excel to give me an error message should the two amounts not balance. I have done this many times without any issues - however I seem to have a problem in some reports whereby I am receiving an error message even when the value in the report matches the value in the table. I have manually checked that the report adds up correctly - and the value in the table. I have checked that neither value includes a spurious amount within the decimal (down to 5 places). I have also split my formula (that produces and error message) in to its two halves - and these both return the same result. I am afraid I can't fathom why I am getting an error message. Can anyone help? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Balanced formula does not balance | Excel Discussion (Misc queries) | |||
Balanced formula does not balance | Excel Discussion (Misc queries) | |||
Cell reading Balanced problem | Excel Worksheet Functions | |||
I can balance a plane but i can not fix a formula. | Excel Discussion (Misc queries) | |||
rent received/balance owed/running balance spreadsheet | Excel Discussion (Misc queries) |