Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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}) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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}) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Excel Open Dialog Improper Sort | Excel Discussion (Misc queries) | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
INFORMATION ON FUNCTIONS IN EXCEL (EX. "IF", VALUE LOOKUP) | Excel Worksheet Functions | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) |