0.1 to 0.99 not recognised or calculated when results from formula

[atin]

Problem- Problem- Excel not recognising or counting values between '0.01 to
0.99' in
cells containing formula. Exact sheet can be downloaded at-
http://www.aensia.com/test.zip

In the sheet you download - I want to know which stock is equal to 0.1, 0.2,
0.3 and so on.

Excel is not giving right values in (stock equal to 0.1, 0.2 and others).

Is there any solution to this.

 

[Joel]

Put in cell N7 the formula below. The mod function with one will only show
th efractional part of the stock.

=mod($L7,1)

Then copy to all cells you want the fraction part to show.

 

[Teethless mama]

=IF(ROUND(L7,2)=0.1,L7,"")

 

[atin]

Thanks for formula, it works. But can you explain the logic of formula. I am
not able to understand it exactly.

 

[JoeU2004]

can you explain the logic of formula.

Referring to your link to
http://spreadsheets.google.com/ccc?key=rJzjkLwANDZqnlQHllepUYQ in another
posting in this thread....

You seem to be wondering why in L54, for example, 2.3+2.25-4.45 appears to
be 0.1, but IF(L54=0.1,L54,"") returns a null string instead of 0.1 (L54).

If you format L54 as Number with 16 decimal places and you format a cell
with the constant 0.1 formatted Number as 16 decimal places, you will see
the reason: L54 is really about 0.0999999999999996, whereas 0.1 is really
about 0.1000000000000000.

(Note: Technically, it does not make sense to format the constant 0.1 with
16 decimal places, since Excel will only format the first 15 significant
digits and fill in the rest with zero. But it makes that comparison with
L54 easier to see and explain.)

I explained the reason in one of my previous postings in this thread, to
wit: "results of even simple arithmetic [do] not always exactly
match the displayed number".

If you format L54 as General or as Number with 1 or 2 decimal places, Excel
will automatically round the __displayed__ value to 0.1. The operative word
is "displayed". Obviously, the underlying value is not really 0.1, since we
can format it to 16 decimal places and see a different value.

Moreover, the difference between the true value in L54 and the constant 0.1
is large enough that Excel considers them to be different. Sometimes Excel
treats different values as equal when they are "close". See the section
"Example When a Value Reaches Zero" in
http://support.microsoft.com/kb/78113. The explanation there is not
entirely accurate. But it might give you some idea about what is going on.


By explicitly rounding L54 to 2 decimal places (or 1 decimal place), you are
forcing the value used in the comparison to be exactly the same as the
constant with that number of decimal places. In other words, when
0999999999999996 is rounded to 2 dp, its result (0.10) exactly matches the
constant 0.10.

Now, you might wonder why 2.3+2.25-4.45 is not exactly the same the constant
0.1. The answer is complicated, and I am not sure you would understand.
The aforementioned link to KB 78113 might give you some insight.
Alternatively, if you what the technical details, post a reply here, and I
will gladly offer what I think is a better explanation.

HTH.


----- original message -----

 

[JoeU2004]

Again, referring to your link to
http://spreadsheets.google.com/ccc?key=rJzjkLwANDZqnlQHllepUYQ in another
posting in this thread....

Alternatively and arguably more reliably, in K54 you could write:

=ROUND(SUM(D54:J54),2)

and in L54 you could write:

=ROUND(B54+C54-K54,2)

and similarly use ROUND in every formula that involves arithmetic with
numbers that might have decimal fractions. Then in N54, you can write
simply =IF(L54=0.1,L54,"") and get the expected results.

The prolific use of ROUND in computational formulas, not just in
comparisons, is more reliable because it minimizes the propagation of the
infinitesimal numerical abberations that I explained in several other
postings in this thread, which are an avoidable consequence of the way that
Excel (and most applications) represent numbers and perform arithmetic
internally.

The use of ROUND ensures that the underlying value exactly matches the
displayed value if you had typed the displayed value as a constant.
Formatting alone does not change the underlying value. For example, L54 is
actually about 0.0999999999999996, but it is displayed as 0.1.


----- original message -----