True/False Excel Statement Issue

Excel uses floating point which is inexact. For example, 0.1 has no exact representation in binary notation, and it introduces errors into calculations. Another example would be calculations that should have the same result that were performed in different ways. I believe Excel tries to deal with this in comparisons by ignoring the least signficant bit or two, but at some point, it doesn't work.

quote:

---

UNLESS I manually input the value rather than let the formula do it

---

That's what I'm talking about. The workaround is to use a tolerance value in your comparisons, but since you're talking about budgets and hence currency, you may be able to use the ROUND function. More generally, you'd subtract the two numbers and compare the absolute value of the difference to your tolerance factor. (From memory, Excel's binary notation is IEEE 754 double precision, a 64 bit number with range 1E+/-308 with 15 decimal digits of precision.)

quote:

---

quote:

---

Excel uses floating point which is inexact. For example, 0.1 has no exact representation in binary notation, and it introduces errors into calculations.

---

This makes total sense now that I think about it. When my budgets are done for a project, I try to get every subtotal of Divisions 1-16 to be a whole dollar, but even when all of the subtotals read "$X.00", the bottom grand total will read $1,500,000.01. It annoys the shite out of me

---

That explanation is inaccurate. Machine and round off error can and do occur but it will not be at the 100ths level. The things you're seeing at x.00 and getting a x.01 in the sum is a result of showing 2 decimal places in the individual cells but you're formula is giving you numbers in the 3rd decimal places that add up to give you the .01.

You can fix this by adding the Round() function in you formula. Assuming you want two decimal points, the new formula should be should be Round(original formula, 2). That should give you exactly what you see when those cells are summed.

quote:

---

I believe my explanation was correct WRT to the message I replied to.

However, it's not really correct to talk about "the 100ths level". As I said, Excel's FP numbers have 15 decimal digits of precision, and the errors I was talking about are limited typically to the least significant digit or two. It might be hundredths, millionths, or whatever, depending on where the decimal point is in a given number. These small errors can and do cause comparisons to fail for numbers computed in different ways.

---

You are indeed correct, I misread something not to mention I am getting old and senile.

Using the round function should correct this issue, I think.

I'm not sure what exactly he's trying to do though.

Round may work, but is also subject to errors if the rounded digit differs and rounds one way or the other.

TRUNC could work as well.

Typically, when comparing values you would use a tolerance value and either ratio or subtract the values and compare against a tolerance.

quote:

---

Round may work, but is also subject to errors if the rounded digit differs and rounds one way or the other.

TRUNC could work as well.

---

ROUND is normally the right choice when dealing with currency. TRUNC is not, because it exposes you to just what you said, the direction in which the rounded digit errs. For example, suppose you are computing x and y in different ways, and FP rounding errors in the least significant bit resulted in the following for some hypothetical format with 8 decimal digits of precision:

x = 1.2500001
y = 1.2499999

Rounding to two places gives you 1.25 for both, while truncating to two places gives you 1.25 and 1.24. Excel has actually got 15 decimal digits, so the errors are that much farther out.

quote:

---

Typically, when comparing values you would use a tolerance value and either ratio or subtract the values and compare against a tolerance.

---

Yeah, I talked about that in my first post as the more general approach to the problem.