So the same expression in VBA might have different results, even ignoring Excel's heuristics.) (FYI, this is not true in VBA expressions. In this case, 13,530.90 and $14,369.10 are really represented internally exactly asġ3530.8999999999,99636202119290828704833984375 andġ4369.1000000000,00363797880709171295166015625, where the comma is my way of demarcating 15 significant digits to the left.Ĭompounding the "problem" (existence of computational abberations) is that when Excel performs the arithmetic, each pairwise operation is rounded to its internal representation. The existence and magnitude of the abberations depend on the relative size of the operands, i.e. This yields infinitesimal numerical "errors" (abberations) in nearly all non-integer numerical computations. The problem, again as explained by the aforementioned references, is that most decimal fractions cannot be represented exactly in binary. So, to explain what is really going on, with the exception of when Excel's heuristics get in the way.
(I have ever been enamored with this heuristic because it is so poorly defined and implemented, IMHO.) In the first case, "-" is not considered the last operation because of the ")". I guess Excel really means: the last operations results in a _change_ that is close to zero. (A1-B1-C1-D1), we get the same non-zero result as the expression A1-B1-C1-D1-0 because the "last subtraction" does not "result" in a value close to zero. What they really mean is: if the _last_ addition or subtraction is "close" to zero and of course, they neglect to define what "close" is.įor the expression A1-B1-C1-D1, the last subtraction is "close" to zero, to Excel "corrects" the value. Should an addition or subtraction operation result in a value at or very close to zero, Excel 97 and later will compensate for any error introduced as a result of converting an operand to and from binary. "Excel 97, however, introduced an optimization that attempts to correct for this problem. The answer is given in the aforementioned reference,, to wit: I also overlooked this statement in the original post, which is the key: Ultimately, I can just override the formula on this row, but it isĪggravating to have it not work properly. I invite others to try this and see if you don't get the same thing. Order and switching the first and second values it works fine. Tied to subtracting the 1st entry with the.
I've experimented with the formula in many ways, but the issue seems to be Last 3 cells, it still gives the strange result. The 3 invoice amounts, the result is fine, and yet if I enter a 0.00 into the Interestingly, if I limit the formula to H = A1-B1-C1-D1 just accounting for Spreadsheet and are not created by formulas, so the issue is not related to The result (or the amount left to bill) should be $0.00, but instead Excel This formula works great in all my rows (several hundred), EXCEPT when the The formula in column H = A1-B1-C1-D1-E1-F1-G1. Each month, I need to know how much is left to bill on each Here's the set-up: We recieve a PO for a project (col A), and then bill theĬustomer each month for 6 months (cols. Using Excel 2003 and have discovered what I can only assume is a glitch.
0 kommentar(er)
