0.1 to 0.99 not recognised or calculated when results from formula

[atin]

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

 

[Kassie]

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

 

[JoeU2004]

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]

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]

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]

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?key=rJzjkLwANDZqnlQHllepUYQ

 

[JoeU2004]

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?key=rJzjkLwANDZqnlQHllepUYQ

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 -----

 

[JoeU2004]

Errata....

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 -----