Excel "if" Bug?
 

=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both" ,2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
Checked cell formatting, etc. All the same as the OK Version.
If I select fx so that the function arguments screen pops up, the first
statement shows as TRUE (althought D337 is blank).
When I check fx on the original sheet, (with a blank D337), the first
statement correctly shows as false.
If I do a copy (from the non-working sheet) and insert onto a new, blank ws,
the formula looks and works fine again.
Does anyone have any idea where I have to look for this one?

Excel "if" Bug?
 

Are you sure D337 is blank? Click on D337 and then press the delete key.
Then press F9 to ensure calculation.


"BEEJAY" wrote in message
...
=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both" ,2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
Checked cell formatting, etc. All the same as the OK Version.
If I select fx so that the function arguments screen pops up, the first
statement shows as TRUE (althought D337 is blank).
When I check fx on the original sheet, (with a blank D337), the first
statement correctly shows as false.
If I do a copy (from the non-working sheet) and insert onto a new, blank
ws,
the formula looks and works fine again.
Does anyone have any idea where I have to look for this one?

Excel "if" Bug?
 

So if you try =ISBLANK() on the cell you think is empty, you get a TRUE,
correct???

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"BEEJAY" wrote in message
...
=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both" ,2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
Checked cell formatting, etc. All the same as the OK Version.
If I select fx so that the function arguments screen pops up, the first
statement shows as TRUE (althought D337 is blank).
When I check fx on the original sheet, (with a blank D337), the first
statement correctly shows as false.
If I do a copy (from the non-working sheet) and insert onto a new, blank
ws,
the formula looks and works fine again.
Does anyone have any idea where I have to look for this one?

Excel "if" Bug?
 

try this...

=IF(trim(D337)="Front",1,IF(trim(D337)="Rear",1,IF (trim(D337)="Both",2,0)))

better still

=IF(or(trim(D337)="Front",trim(D337)="Rear"),1,IF( D337="Both",2,0))


--
HTH...

Jim Thomlinson


"BEEJAY" wrote:

=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both" ,2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
Checked cell formatting, etc. All the same as the OK Version.
If I select fx so that the function arguments screen pops up, the first
statement shows as TRUE (althought D337 is blank).
When I check fx on the original sheet, (with a blank D337), the first
statement correctly shows as false.
If I do a copy (from the non-working sheet) and insert onto a new, blank ws,
the formula looks and works fine again.
Does anyone have any idea where I have to look for this one?

Excel "if" Bug?
 

=ISBLANK() returns with TRUE - CORRECT
Further: All 13 of my Contracts were "built" using the copy and insert
method without any problem, to-date.
I've also opened the file by the normal file-Open method, rather than thru
my custom menu, than manually recreated the line (ie: not copied and pasted),
but that does not show a positive result, either.

Any further ideas?

"Ken Wright" wrote:

So if you try =ISBLANK() on the cell you think is empty, you get a TRUE,
correct???

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"BEEJAY" wrote in message
...
=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both" ,2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
Checked cell formatting, etc. All the same as the OK Version.
If I select fx so that the function arguments screen pops up, the first
statement shows as TRUE (althought D337 is blank).
When I check fx on the original sheet, (with a blank D337), the first
statement correctly shows as false.
If I do a copy (from the non-working sheet) and insert onto a new, blank
ws,
the formula looks and works fine again.
Does anyone have any idea where I have to look for this one?

Excel "if" Bug?
 

Greetings:
Thanks for your input.
I tried your better still suggestion. When D377 is blank, it returns VALUE.
The other parts work as expected.
In the meantime, I have to get these contracts out, so I am using the
following, for now.
=IF(D336="",0,IF(D336="Both",2,IF(D336="Front",1,I F(D336="Rear",1,0))))
I don' t like it, but it seems to work, and since the D336 has a 3 option
validation on it, I don't think I can go wrong.

However, IF something comes to mind sometime, I'd still like to know what
the problem is.

From what I have been reading here and there, since my contracts are
constantly worked on ( insert lines, delete lines, etc.), apparently, it
would be advisable to "re-build" the contracts from scratch (cut and paste
sections into a new work-book). Apparently, this will likely leave a large
amount of "garbage" behind, IF I'm understanding this all correctly. The
same goes for all my macros, again, as I understand it.

Meanwhile, thanks for the time spent on puzzling this thru, and all the
suggestions.


"Jim Thomlinson" wrote:

try this...

=IF(trim(D337)="Front",1,IF(trim(D337)="Rear",1,IF (trim(D337)="Both",2,0)))

better still

=IF(or(trim(D337)="Front",trim(D337)="Rear"),1,IF( D337="Both",2,0))


--
HTH...

Jim Thomlinson


"BEEJAY" wrote:

=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both" ,2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
Checked cell formatting, etc. All the same as the OK Version.
If I select fx so that the function arguments screen pops up, the first
statement shows as TRUE (althought D337 is blank).
When I check fx on the original sheet, (with a blank D337), the first
statement correctly shows as false.
If I do a copy (from the non-working sheet) and insert onto a new, blank ws,
the formula looks and works fine again.
Does anyone have any idea where I have to look for this one?

Excel "if" Bug?
 

BEEJAY wrote...
=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both ",2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
....

Whenever a formula works differently on different worksheets, the
*FIRST* thing to check is the Transition Formula Evaluation setting.
Run the menu command Tools Options, select the Transition tab, and
check whether the box to the left of 'Transition formula evaluation'
near the bottom of the dialog is checked. If it is, that's the
problem. Transition Formula Evaluation means Lotus 123 Release 2.01 &
subsequent formula evaluation semantics, and that means *ALL* strings
equal numeric zero when compared to any number, and blank cells are
*ALWAYS* evaluated as numeric zero. Thus any string equals any blank
cell.

It may or may not be safe to disable Transition Formula Evaluation. You
could have other formulas that rely on it. That's up to you to check.

Another safe way to perform the same operation that should work
whatever this setting would be

=SUMPRODUCT(COUNTIF(D337,{"Front";"Rear";"Both"}), {1;1;2})

Excel "if" Bug?
 

Harlan:
Fortunately, The Transition Formula Evaluation box is NOT checked.
I used your suggestion, in place of my *******ized IF statement.
This one looks good and is easy to "read". I expect to use it lots more.
Thanks so much.

Again, I'd still like to get to the bottom of the problem.


"Harlan Grove" wrote:

BEEJAY wrote...
=IF(D337="Front",1,IF(D337="Rear",1,IF(D337="Both ",2,0)))
Formula created on new, blank sheet - Works Great
Copy to Contract Sheets - Big Problems
Formula will not return a Zero, (as expected) when D337 is Blank
....

Whenever a formula works differently on different worksheets, the
*FIRST* thing to check is the Transition Formula Evaluation setting.
Run the menu command Tools Options, select the Transition tab, and
check whether the box to the left of 'Transition formula evaluation'
near the bottom of the dialog is checked. If it is, that's the
problem. Transition Formula Evaluation means Lotus 123 Release 2.01 &
subsequent formula evaluation semantics, and that means *ALL* strings
equal numeric zero when compared to any number, and blank cells are
*ALWAYS* evaluated as numeric zero. Thus any string equals any blank
cell.

It may or may not be safe to disable Transition Formula Evaluation. You
could have other formulas that rely on it. That's up to you to check.

Another safe way to perform the same operation that should work
whatever this setting would be

=SUMPRODUCT(COUNTIF(D337,{"Front";"Rear";"Both"}), {1;1;2})