Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from formula
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from formula
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. "atin" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from formula
=IF(ROUND(L7,2)=0.1,L7,"")
"atin" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
Thanks for formula, it works. But can you explain the logic of formula. I am
not able to understand it exactly. "Teethless mama" wrote: =IF(ROUND(L7,2)=0.1,L7,"") "atin" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
"atin" wrote:
can you explain the logic of formula. Referring to your link to http://spreadsheets.google.com/ccc?k...DZqnlQHllepUYQ 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. "Teethless mama" wrote: =IF(ROUND(L7,2)=0.1,L7,"") 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 ----- "atin" wrote in message ... Thanks for formula, it works. But can you explain the logic of formula. I am not able to understand it exactly. "Teethless mama" wrote: =IF(ROUND(L7,2)=0.1,L7,"") "atin" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
"atin" wrote:
"Teethless mama" wrote: =IF(ROUND(L7,2)=0.1,L7,"") Again, referring to your link to http://spreadsheets.google.com/ccc?k...DZqnlQHllepUYQ 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 ----- "atin" wrote in message ... Thanks for formula, it works. But can you explain the logic of formula. I am not able to understand it exactly. "Teethless mama" wrote: =IF(ROUND(L7,2)=0.1,L7,"") "atin" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
0.1 to 0.99 not recognised or calculated when results from formula | Excel Worksheet Functions | |||
Need to autofil a formula that is not recognised? | Excel Discussion (Misc queries) | |||
Is this formula 100*(PRODUCT(1+C3:N3/100)-1) recognised by Excel | Excel Worksheet Functions | |||
Can Function results be calculated? | New Users to Excel | |||
= not recognised as formula (sometimes) | Excel Discussion (Misc queries) |