Forum Discussion
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,"")))
- 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, "")), ""))
