SOLVED

Excel challenge calculating when a formula leaves a blank cell

Copper Contributor

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 there is a number in cell P or Q but not both, when I want it to calculate P-Q. There are formulas in both cells but if the answer is blank in one of the cells when there is a number in the other, I still want to subtract P-Q. Only when the answer in both cells is blank, I want the answer to be blank rather than calculating p-q.             =IF(AND(ISBLANK(P10),ISFORMULA(Q10)),Q10,IF(AND(ISBLANK(Q10),ISFORMULA(P10)),P10,IF(AND(ISNUMBER(P10),ISNUMBER(Q10)),P10-Q10,"")))

8 Replies
best response confirmed by RobertsTrying (Copper Contributor)
Solution

@RobertsTrying 

As variant

=IF( (P10="")*(Q10=""), "", P10-Q10)
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.
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, "")), ""))

@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 

IMHO, AI overcomplicated it

Thanks so much Sergei. This is so much simpler than the AI suggestion, and it works!!
I learned something new with the N() function. Thanks again Sergei. This is very helpful now and for future reference.

@RobertsTrying , you are welcome, glad to help

1 best response

Accepted Solutions
best response confirmed by RobertsTrying (Copper Contributor)
Solution

@RobertsTrying 

As variant

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

View solution in original post