1

I noticed something strange when using named cells in an IF condition in Excel 365 and I would like to know what's going on.

Let's say I have a named cell MyValue, whose value = 15. Then, the following works fine: =IF(MyValue > 10| "Big"| "Small"). The cell with the formula will contain the value "Big".

However, the following causes an error to occur: =IF(10 < MyValue| "Big" | "Small")

Obviously, this can easily be worked around, but it doesn't make sense to me why it should not work both ways. Is this a bug or am I missing something?

Below screenshots of my workbook show the Name Manager, both versions of the formula and error resulting from "offending" formula.

The working version of the formula, with Name Manager

The offending version of the formula and resulting error

Improve this question
4
  • 3
    can you edit the post to include the name manager so we can see the formula of the named range? I cannot reproduce your error. I can use either formula and both return Big to the range. Also let us know what error you get.
    – Scott Craner
    Commented Dec 22, 2021 at 17:32
  • 1
    Like @ScottCraner, I am unable to reproduce your error.
    – Reddy Lutonadio
    Commented Dec 22, 2021 at 17:35
  • Try putting a space between MyValue and the |. The only thing I can guess is the the formula thinks that the | is part of the name...
    – Scott Craner
    Commented Dec 22, 2021 at 17:52
  • Your post contains ; but the screenshot shows | - why?
    – harrymc
    Commented Dec 22, 2021 at 18:14

1 Answer 1

Reset to default
0

Scott Craner's comment was spot-on: adding a space between MyValue and | solved it.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .