I need to subtract cells, but when one or both of them is blank, or contains text, the subtract formula returns a #VALUE! error. I am using =SUM(A1-B1).
Can anyone please help with the formula which will treat A1 as 0 (or ignore it) if it is blank or contains text, and will separately treat B1 as 0 (or ignore it) if it is blank or contains text?
(I don't want to change the precedent cells themselves.) I can't figure out how to structure IF, AND and ISNUMBER.
3 Answers
=SUM(IF(ISNUMBER(C38),(C38),0)-IF(ISNUMBER(K38),(K38),0)) seems to do the trick - for fellow sufferers :)
Here is a shorter formula :
=IFERROR(A1+0,0)-IFERROR(B1+0,0)
+0 is used to force converting to numeric.
Try this in C1 and copied down:
=N(A1)-N(B1) Regards
