Forum Discussion

RobertsTrying's avatar
RobertsTrying
Copper Contributor
Jun 18, 2024
Solved

Excel challenge calculating when a formula leaves a blank cell

Wondering if anyone can make this formula work? I just want to subtract 2 numbers but there are formulas in the reference and resulting cells. The following formula results in a blank answer when the...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 18, 2024

RobertsTrying 

As variant

=IF( (P10="")*(Q10=""), "", P10-Q10)
  • RobertsTrying's avatar
    RobertsTrying
    Copper Contributor
    Jun 18, 2024
    Thanks for trying but if one of the cells (p or q) is blank, your formula gives me a #Value! error
    because there is a formula in both cells.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 18, 2024

      RobertsTrying 

      I see. So actually you could have empty strings in the cells. Formula can't return blank as value. WE may use

      =IF( (P10="")*(Q10=""), "", N(P10)-N(Q10) )

      N() applied to any text (includes empty string) returns zero.

      • RobertsTrying's avatar
        RobertsTrying
        Copper Contributor
        Jun 18, 2024
        I learned something new with the N() function. Thanks again Sergei. This is very helpful now and for future reference.
    • RobertsTrying's avatar
      RobertsTrying
      Copper Contributor
      Jun 18, 2024
      I figured it out with Bing AI. This formula works in all conditions:

      =IF(AND(ISNUMBER(P10), ISNUMBER(Q10)), P10 - Q10, IF(OR(ISFORMULA(P10), ISFORMULA(Q10)), IF(ISNUMBER(P10), P10, IF(ISNUMBER(Q10), -Q10, "")), ""))

Resources