A Curious OR

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?

formula bar showing misuse of or

By the way, the proper syntax for the formula is =IF(OR(ISBLANK(D9),ISBLANK(E9)),"",D9+E9)

Posted in Uncategorized

13 thoughts on “A Curious OR

  1. I just tried the formula in F8, the OR remains capitalized, still gets the same result (Excel 2000).

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

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

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

  5. “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.

  6. 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 :-).

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

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

  9. 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. :)

  10. 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)

  11. 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?

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

Leave a Reply

Your email address will not be published. Required fields are marked *