I received this file and I can’t figure out a formula. When I retype a similar formula, the capitalization of OR turns into oR. Here’s what’s happening as far as I can tell: The construct ISBLANK(invalidname) does not produce an error, but produces FALSE. The invalid name produces a #NAME? error when used by itself, but that #NAME? error doesn’t propagate beyond ISBLANK. That, in itself, seems like a bug to me. But it still doesn’t explain why the capitalization of OR changes when used in this manner. There are no defined names that I can see. Anyone?
By the way, the proper syntax for the formula is =IF(OR(ISBLANK(D9),ISBLANK(E9)),"",D9+E9)
I just tried the formula in F8, the OR remains capitalized, still gets the same result (Excel 2000).
The only way I could duplicate this was to define a name called oR. Since you said there were no defined names, I deleted it to try something else and stumbled across the answer (or at least one answer). Even after I deleted the name, the capitalization
reverted to the oR that I had in the now-deleted defined name.
Just another bug.
I have had capitalization problems with .Value changing to “.value” in the VBA editor before.
I tried it in Excel 2000 and got the same results as Terry. The OR remained capitalized and the formula added the values in columns D and E regardless of whether or not they contained a blank.
I think the point was that this is happening with a specific workbook. zfraile has hit the nail on the head; Excel somehow remembers the deleted name.
“Excel somehow remembers the deleted name.”
Maybe, but I’d be VERY surprised if the person who sent this workbook ever used a defined name. I guess I’ll ask.
Could very easily have been accidental; e.g., typing oR into the Name Box and hitting Enter. Of course, that doesn’t explain how it got deleted if the originator of the workbook is as clueless as you say :-).
Name a cell “or” then delete the cell. Now the “oR” will be replaced by “or” in the formula. If you copy the range to a new book the formula will still read “or” but there will be no named range. Weird…
One more problem with the erased defined names.
In cell A1 I have the error #NAME?.
I have the error from a defined name that does not exist.
I check this error with the function = ISERROR(A1)
I use the keyboard.
All right, I take TRUE.
Now I do the same, with “Insert Function Box.”
Surprise! Now I take FALSE.
It really makes my day that you guys use my Gemini template. Every time. I always like to think that if I get stuck on the macros for my annual accounts then at least someone here might help me out. :). Good luck to you all. Open Source rocks. :)
loannis:
I tried what you described. Surprisingly, Excel produced this function in case one uses the function wizard:
=ISERROR(“A1?)
(there are quotes around the address, might not show in the post )
Instead of
=ISERROR(A1)
(without quotes, as expected)
The argument for the ISBLANK function looks like an improper logical construct. It looks like a text entry, which might mean the “OR” transform is a Tools-Autocorrect problem?
As written, your IF function’s first argument resolves to a FALSE condition so that the function’s evaluation skips the second argument and uses the third.
I think you need a formula like the following:
=IF(OR(ISBLANK(D8),ISBLANK(E8)),””,D8+E8)
Note that the second argument (“”) produces a non-numeric outcome (if that is your intent). You may want to use a zero instead.