Forum Discussion
How do you write a formula that must have two conditions met?
Making progress, thanks to the help of the kind people here! I Have this formula:
=IF(D1-C1<0,"",D1-C1) I'm REALLY proud of myself here, at this point! It worked properly.
However, when both C and D are empty, it displays #VALUE (I think it has a symbol with it). This is what I'm trying to correct. How Do I tell excel that if both cells are empty, I want the operation to display nothing?
6 Replies
The easiest way is simply to put SUM() around your cell references:
=IF(SUM(D1)-SUM(C1)<0,"",SUM(D1)-SUM(C1))
Dan Elgaard Whyever would you want to wrap each cell reference into a SUM function? this accomplishes absolutely nothing and the result is exactly the same as with the formula in the initial question. By all means, use Sum() if you want to create a sum of something, or use Sum() to create some hot formula tricks, but don't use it if it does not add value. That just increases the calculation load for Excel.
Hello,
if you want to check if both cells contain numbers, you can use Count(). Combine that with your existing condition like this:
=IF(or(D1-C1<0,count(C1:D1)=0),"",D1-C1)
Or, written another way:
=if(and(Count(C1:D1)=2,D1-C1>0),D1-C1,"")
In words: if both cells C1 and D1 contain a number AND if at the same time the result of the subtraction is greater than 0, then perform the subtraction, else return a blank.
Does that help?
Thank you!! Im going to give this a try. If I may, Id like to ask exactly what this formula is saying, and what is the purpose of the =if(and(Count(C1:D1)=2,D1-C1>0),D1-C1,"") part of the formula? I want to fully understand what the language is, rather than lean on everyone. Your help is greatly appreciated!
Hello,
the Count() function counts the cells that have numbers (not text). So, in this formula, if the Count() of cells C1 to D1 returns the result 2, that means that both of these cells contain a number. Used in an IF() statement, Count is a great way to ensure that a calculation is only performed when all required input cells have been filled in.
Both empty:
=IF(AND(D1="";F1="");"";IF(D1-C1<0,"",D1-C1))
At least 1 empty (and obviously also both):
=IF(OR(D1="";F1="");"";IF(D1-C1<0,"",D1-C1))
