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
      Thanks so much Sergei. This is so much simpler than the AI suggestion, and it works!!
  • 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