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- 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from formula
You say you want to calculate stock which is less than 0.2 yet
You use a formula to pick up stock which is equal to 0.2, as well as the various other levels in your sheet. Obviously, if you use a formula which checks whether a level is equal to, you will not get the same result as when you check for less than. Not clear at all what you are trying to achieve here? What about stock which shows zero? What about stock which does not meet your requirements, but are in any case less than 1? Can't you use one column, and just ckeck for anything less than 1? -- HTH Kassie Replace xxx with hotmail "atin" wrote: 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from formula
There are so many inconsistencies in your posting, it is difficult to know
what question(s) you are really asking. I suspect the root cause of whatever problem you are having is the fact that most decimal fractions are not represented exactly in Excel (and most applications). So results of even simple arithmetic does not always exactly match the displayed number. For example, what appears to be 0.2, resulting from a formula, might not exactly match the constant 0.2 that you might enter. Generally, the solution is to use ROUND one way or another. For example, where you have =formula in a cell formatted as General or even as Number with 2 decimal places, you might change that to =ROUND(formula,2) if your intention is for the result to match a constant or other formula results to within 2 decimal places. Do not rely on formatting to accomplish that. That only affects the appearance of the value; it does not affect the exact underlying value. Alternatively, for example, where you have IF(D2=0.2,...), you could write IF(ROUND(D2,1)=0.2,...). I hope that addresses some, if not all, of your problem somehow. ----- original message ----- "atin" wrote in message ... 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
I am trying to show stock which is equal to 0.1, 0.2, 0.3 and others. It is
important for knowing me which stock is equal to above values. Excel is not giving right values in (stock equal to 0.1, 0.2 & others). Stock less than 1 donot solve my purpose. You first download sheet at- http://www.aensia.com/test.zip "JoeU2004" wrote: There are so many inconsistencies in your posting, it is difficult to know what question(s) you are really asking. I suspect the root cause of whatever problem you are having is the fact that most decimal fractions are not represented exactly in Excel (and most applications). So results of even simple arithmetic does not always exactly match the displayed number. For example, what appears to be 0.2, resulting from a formula, might not exactly match the constant 0.2 that you might enter. Generally, the solution is to use ROUND one way or another. For example, where you have =formula in a cell formatted as General or even as Number with 2 decimal places, you might change that to =ROUND(formula,2) if your intention is for the result to match a constant or other formula results to within 2 decimal places. Do not rely on formatting to accomplish that. That only affects the appearance of the value; it does not affect the exact underlying value. Alternatively, for example, where you have IF(D2=0.2,...), you could write IF(ROUND(D2,1)=0.2,...). I hope that addresses some, if not all, of your problem somehow. ----- original message ----- "atin" wrote in message ... 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
"atin" wrote:
You first download sheet at- http://www.aensia.com/test.zip I am unable to look at the xlsx file in that zip file. Without it, I am unable to understand what you are asking for. ----- original message ----- "atin" wrote in message ... I am trying to show stock which is equal to 0.1, 0.2, 0.3 and others. It is important for knowing me which stock is equal to above values. Excel is not giving right values in (stock equal to 0.1, 0.2 & others). Stock less than 1 donot solve my purpose. You first download sheet at- http://www.aensia.com/test.zip "JoeU2004" wrote: There are so many inconsistencies in your posting, it is difficult to know what question(s) you are really asking. I suspect the root cause of whatever problem you are having is the fact that most decimal fractions are not represented exactly in Excel (and most applications). So results of even simple arithmetic does not always exactly match the displayed number. For example, what appears to be 0.2, resulting from a formula, might not exactly match the constant 0.2 that you might enter. Generally, the solution is to use ROUND one way or another. For example, where you have =formula in a cell formatted as General or even as Number with 2 decimal places, you might change that to =ROUND(formula,2) if your intention is for the result to match a constant or other formula results to within 2 decimal places. Do not rely on formatting to accomplish that. That only affects the appearance of the value; it does not affect the exact underlying value. Alternatively, for example, where you have IF(D2=0.2,...), you could write IF(ROUND(D2,1)=0.2,...). I hope that addresses some, if not all, of your problem somehow. ----- original message ----- "atin" wrote in message ... 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
Link is working- http://www.aensia.com/test.zip (download without any
download accelerator) or it is accessible through google docs http://spreadsheets.google.com/ccc?k...DZqnlQHllepUYQ "JoeU2004" wrote: "atin" wrote: You first download sheet at- http://www.aensia.com/test.zip I am unable to look at the xlsx file in that zip file. Without it, I am unable to understand what you are asking for. ----- original message ----- "atin" wrote in message ... I am trying to show stock which is equal to 0.1, 0.2, 0.3 and others. It is important for knowing me which stock is equal to above values. Excel is not giving right values in (stock equal to 0.1, 0.2 & others). Stock less than 1 donot solve my purpose. You first download sheet at- http://www.aensia.com/test.zip "JoeU2004" wrote: There are so many inconsistencies in your posting, it is difficult to know what question(s) you are really asking. I suspect the root cause of whatever problem you are having is the fact that most decimal fractions are not represented exactly in Excel (and most applications). So results of even simple arithmetic does not always exactly match the displayed number. For example, what appears to be 0.2, resulting from a formula, might not exactly match the constant 0.2 that you might enter. Generally, the solution is to use ROUND one way or another. For example, where you have =formula in a cell formatted as General or even as Number with 2 decimal places, you might change that to =ROUND(formula,2) if your intention is for the result to match a constant or other formula results to within 2 decimal places. Do not rely on formatting to accomplish that. That only affects the appearance of the value; it does not affect the exact underlying value. Alternatively, for example, where you have IF(D2=0.2,...), you could write IF(ROUND(D2,1)=0.2,...). I hope that addresses some, if not all, of your problem somehow. ----- original message ----- "atin" wrote in message ... 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
"atin" wrote:
Link is working- http://www.aensia.com/test.zip (download without any download accelerator) Obviously the link works for me; that's how I knew the zip file contains an xlsx file. I meant that I am not capable of opening xlsx files. Apparently that requires software that I do not have. or it is accessible through google docs http://spreadsheets.google.com/ccc?k...DZqnlQHllepUYQ That helps a little. Unfortunately, I am unable to see the formulas in cells, other than the "formulas" that you include in title cells (e.g. L6). In particular, I cannot see the formula in N77 (et al), which you I presume you highlighted to draw attention to it. Perhaps I simply do not know how to use spreadsheets.google.com. No matter. I see that "Teethless mama" provided you with a solution that you seem satisfied with -- a solution which is similar to the general ideas I tried to help you. I will make additional comments in follow-ups to that part of the thread. ----- original message ----- "atin" wrote in message ... Link is working- http://www.aensia.com/test.zip (download without any download accelerator) or it is accessible through google docs http://spreadsheets.google.com/ccc?k...DZqnlQHllepUYQ "JoeU2004" wrote: "atin" wrote: You first download sheet at- http://www.aensia.com/test.zip I am unable to look at the xlsx file in that zip file. Without it, I am unable to understand what you are asking for. ----- original message ----- "atin" wrote in message ... I am trying to show stock which is equal to 0.1, 0.2, 0.3 and others. It is important for knowing me which stock is equal to above values. Excel is not giving right values in (stock equal to 0.1, 0.2 & others). Stock less than 1 donot solve my purpose. You first download sheet at- http://www.aensia.com/test.zip "JoeU2004" wrote: There are so many inconsistencies in your posting, it is difficult to know what question(s) you are really asking. I suspect the root cause of whatever problem you are having is the fact that most decimal fractions are not represented exactly in Excel (and most applications). So results of even simple arithmetic does not always exactly match the displayed number. For example, what appears to be 0.2, resulting from a formula, might not exactly match the constant 0.2 that you might enter. Generally, the solution is to use ROUND one way or another. For example, where you have =formula in a cell formatted as General or even as Number with 2 decimal places, you might change that to =ROUND(formula,2) if your intention is for the result to match a constant or other formula results to within 2 decimal places. Do not rely on formatting to accomplish that. That only affects the appearance of the value; it does not affect the exact underlying value. Alternatively, for example, where you have IF(D2=0.2,...), you could write IF(ROUND(D2,1)=0.2,...). I hope that addresses some, if not all, of your problem somehow. ----- original message ----- "atin" wrote in message ... 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
0.1 to 0.99 not recognised or calculated when results from for
Errata....
I wrote: In particular, I cannot see the formula in N77 (et al) That should read N54. I was looking at the "Sr no." in column A. ----- original message ----- "JoeU2004" wrote in message ... "atin" wrote: Link is working- http://www.aensia.com/test.zip (download without any download accelerator) Obviously the link works for me; that's how I knew the zip file contains an xlsx file. I meant that I am not capable of opening xlsx files. Apparently that requires software that I do not have. or it is accessible through google docs http://spreadsheets.google.com/ccc?k...DZqnlQHllepUYQ That helps a little. Unfortunately, I am unable to see the formulas in cells, other than the "formulas" that you include in title cells (e.g. L6). In particular, I cannot see the formula in N77 (et al), which you I presume you highlighted to draw attention to it. Perhaps I simply do not know how to use spreadsheets.google.com. No matter. I see that "Teethless mama" provided you with a solution that you seem satisfied with -- a solution which is similar to the general ideas I tried to help you. I will make additional comments in follow-ups to that part of the thread. ----- original message ----- "atin" wrote in message ... Link is working- http://www.aensia.com/test.zip (download without any download accelerator) or it is accessible through google docs http://spreadsheets.google.com/ccc?k...DZqnlQHllepUYQ "JoeU2004" wrote: "atin" wrote: You first download sheet at- http://www.aensia.com/test.zip I am unable to look at the xlsx file in that zip file. Without it, I am unable to understand what you are asking for. ----- original message ----- "atin" wrote in message ... I am trying to show stock which is equal to 0.1, 0.2, 0.3 and others. It is important for knowing me which stock is equal to above values. Excel is not giving right values in (stock equal to 0.1, 0.2 & others). Stock less than 1 donot solve my purpose. You first download sheet at- http://www.aensia.com/test.zip "JoeU2004" wrote: There are so many inconsistencies in your posting, it is difficult to know what question(s) you are really asking. I suspect the root cause of whatever problem you are having is the fact that most decimal fractions are not represented exactly in Excel (and most applications). So results of even simple arithmetic does not always exactly match the displayed number. For example, what appears to be 0.2, resulting from a formula, might not exactly match the constant 0.2 that you might enter. Generally, the solution is to use ROUND one way or another. For example, where you have =formula in a cell formatted as General or even as Number with 2 decimal places, you might change that to =ROUND(formula,2) if your intention is for the result to match a constant or other formula results to within 2 decimal places. Do not rely on formatting to accomplish that. That only affects the appearance of the value; it does not affect the exact underlying value. Alternatively, for example, where you have IF(D2=0.2,...), you could write IF(ROUND(D2,1)=0.2,...). I hope that addresses some, if not all, of your problem somehow. ----- original message ----- "atin" wrote in message ... 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 My worksheet- A B C D E 1 Stock Open Sale return Sale Stock should be Calculate if D=0.2 2 0.65 0.45 0.2 0.2 3 5 1 4 0 4 2 2 0 0 5 7 6.8 0.2 0 6 6 5.5 0.5 0 7 9 8.1 0.9 0 Stock should be i.e. D2 to D7 contains formula =A2+B2-C2 I want to calcuate stock which is 'less than 0.2' in 'stock should be' i.e. D2 to D7. I used formula =IF(D2=0.2,D2,0) As you can see it gives right value for D2 but is not giving right value for D5. Can anyone help. In my sheet excel is not recognising values starting from 0.01 to 0.99 in D2 to D7 as it is calculated by formula. You can download the original sheet giving problem at- http://www.aensia.com/test.zip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Formating not recognised in lookup formula | Excel Discussion (Misc queries) | |||
Can Function results be calculated? | New Users to Excel | |||
= not recognised as formula (sometimes) | Excel Discussion (Misc queries) |