如何将多分支IF语句转为Excel嵌套IF函数并处理空值判断问题

Let's start by unpacking why your current formula is acting up: when V2 is blank, Excel treats it as a 0 in numeric comparisons. So AS2 > V2 ends up being equivalent to AS2 > 0—which will almost always be true if AS2 holds a positive date or number, leading to those incorrect "RETROSPECTIVE" results you're seeing.

To fix this, we need to add a check for non-blank V2 before evaluating the CONCURRENT condition. Here's the adjusted formula that handles blank V2 cells correctly:

=IF(AS2 <= J2, "PROSPECTIVE",
  IF(AND(NOT(ISBLANK(V2)), AS2 >= J2, AS2 <= V2), "CONCURRENT",
    IF(OR(AS2 > H2, (NOT(ISBLANK(V2)) AND AS2 > V2)), "RETROSPECTIVE", "UNKNOWN")
  )
)

Let's walk through the updated logic step by step: #

• First check: If AS2 is less than or equal to J2, return "PROSPECTIVE" (this part stays the same as your original formula).
• Second check: We only return "CONCURRENT" if two things are true: V2 is not blank, AND AS2 falls between J2 and V2 (inclusive). If V2 is blank, we skip this check entirely and move to the next layer.
• Third check: Return "RETROSPECTIVE" if either AS2 is greater than H2, OR (V2 is not blank AND AS2 is greater than V2). This way, we never evaluate AS2 > V2 when V2 is empty, avoiding that false positive.
• Fallback: If none of the above conditions are met, return "UNKNOWN".

If you're using a newer Excel version, you could also use IFBLANK as a shorthand for NOT(ISBLANK()), but the formula above works across all versions and keeps the logic clear.

内容的提问来源于stack exchange，提问作者user3007130