Excel - Return zero in cell that contains a function

December 2016


I need to return zero within a cell when the cell contains a function already:

I have this function already, but cannot paste the function with in cell 1, because this contains 5. I do not want this cell to change unless cell 2 has a number in there.




cell 1 = 5
cell 2 = when a number is imputed within this cell 1-100, i want "cell 1" to change "cell 1" to a zero.

Even if the "cell 1 contains "28", i still want "cell 1" to return zero when cell 2 has a number imputed from 1-150.
Please help!!


See if I got you correct:

A. Cell 1 has a formula IF(OR(L3>1,),0,K3) which at the moment is showing "5"
B. Cell 2 will have a value with in range of 1 to 100
C. If cell 2 has a number between 1 and 100, you want to cell to show 0 and if if cell 2 does not have a number you want to maintain your old formula /value which was IF(OR(L3>1,),0,K3)

If all is correct then:
=IF(AND(a2>=1, A2<=100), 0, IF(OR(L3>1,),0,K3) ) 

In this example
A2 is the cell 2
and the formula is saying that if there is a number between 1 to 100 (including 1 and 100), then show 0 in cell 1. If the value is not between 1 and 100 then show what comes out by formula:


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Return zero in cell that contains a function » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.