Incorrect logic evaluation

[Terry Plampin]

I have a worksheet that returns TRUE when comparing a blank or zero entry to
the criteria. Other worksheets in the same workbook return correctly FALSE
when the contents of the cell do not match the criteria exactly. How can I
fix this problem?

 

[Bernard Liengme]

You have bee sparse on details. Are you comparing numbers? Are you familiar
with round-off errors results from the decimal to binary conversion that all
computer apps must do?
If you want to compare a value in A1 with the number like 2.5 use
=ROUND(A1,12)=2.5, or if you are working with less precision
=ROUND(A1,6)=2.5
To see if two cells have the same value use =ABS(A1-A2)=<1E-12
which test to see if they differ by less than 1 over 1 billion
best wishes
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Terry Plampin]

I'll try to be more precise.
I am looking for a text string, for example =IF(A1="K",1,0). I expect, and
have recieved on other worksheets, FALSE, when anything, other than K,
including nothing and 0, is in the cell. The problem is that on this
particular worksheet, I get TRUE for the condition when the cell is empty or
contains a zero. This is messing up more complicated counting formulas I want
to use.

 

[Niek Otten]

Hi Terry,

<for example =IF(A1="K",1,0) >

Is this an example or is this your exact formula? Please supply your exact formula, not some mockup.

The formula you posted returns either 0 or 1, not TRUE or FALSE, as you suggest.

So what exactly are your formulas, argument values and results?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| I'll try to be more precise.
| I am looking for a text string, for example =IF(A1="K",1,0). I expect, and
| have recieved on other worksheets, FALSE, when anything, other than K,
| including nothing and 0, is in the cell. The problem is that on this
| particular worksheet, I get TRUE for the condition when the cell is empty or
| contains a zero. This is messing up more complicated counting formulas I want
| to use.
| --
| Terry Plampin
|
|
| "Bernard Liengme" wrote:
|
| > You have bee sparse on details. Are you comparing numbers? Are you familiar
| > with round-off errors results from the decimal to binary conversion that all
| > computer apps must do?
| > If you want to compare a value in A1 with the number like 2.5 use
| > =ROUND(A1,12)=2.5, or if you are working with less precision
| > =ROUND(A1,6)=2.5
| > To see if two cells have the same value use =ABS(A1-A2)=<1E-12
| > which test to see if they differ by less than 1 over 1 billion
| > best wishes
| > Bernard V Liengme
| > Microsoft Excel MVP
| > http://people.stfx.ca/bliengme
| > remove caps from email
| >
| > | > >I have a worksheet that returns TRUE when comparing a blank or zero entry
| > >to
| > > the criteria. Other worksheets in the same workbook return correctly FALSE
| > > when the contents of the cell do not match the criteria exactly. How can I
| > > fix this problem?
| > > --
| > > Terry Plampin
| >
| >
| >

 

[Ron Rosenfeld]

I'll try to be more precise.
I am looking for a text string, for example =IF(A1="K",1,0). I expect, and
have recieved on other worksheets, FALSE, when anything, other than K,
including nothing and 0, is in the cell. The problem is that on this
particular worksheet, I get TRUE for the condition when the cell is empty or
contains a zero. This is messing up more complicated counting formulas I want
to use.

You are terribly imprecise in what you write. Your formula cannot possibly
return FALSE, only 1 or 0. Also, you fail to mention what version of Excel you
are using, as that makes a difference as to whether you really mean "worksheet"
or "workbook".

But it may be that you have given us an incorrect formula, or that you meant 0
when your wrote FALSE.

It may be that you are using Excel 2007, or that you are using 2003 or earlier
and meant workbook instead of worksheet.

This is important in telling you how to fix things; but it is likely that you
have Transition Formula Evaluation selected. Exactly where to find this
depends on the version of Excel you are using.

If you indeed mean sheet instead of book, then select the office button in the
upper left hand corner; Excel Options at the bottom and go to the Advanced
option. Then scroll down to the bottom looking for Lotus compatibility
settings for this particular sheet.

It's in a different place if you are using 2003.
--ron