Forum Discussion
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 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
- 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 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.
