return zero in cell that contains a function Solved/Closed

Question

Hello,

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. 

example

=IF(OR(L3>1,),0,K3)

example

cell 1 = 5
cell 2 = when a number is imputted 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 imputted from 1-150
Please help

This would be very much appreciated

seb

rizvisa1 · Answer

Are you saying that 
Cell 1 contains the formula   =IF(OR(L3>1,),0,K3)  which currently shows value 5 and you want it to show 0 there if there is a number in cell 2 ?

If that is the case, lets say cell 2 is A2 and cell 1 is A1

change your formula to
=IF(ISNUMBER(a2), 0, IF(OR(L3>1,),0,K3) )

Basically saying if A2 is a number then show in a1 0, else show what used to be there as per your old formula

seb · Answer

Hi there, thanks for a quick reply

This is how it is:

for instance cell 1 contains value "5" but i want this to stay this value, unless cell 2 has number 1-150 imputted

Example

Forecast cost: cell 1 = 5
Actual cost: cell 2 = 1-150
If cell 2 has data imputted with 1-150, cell 1 returns a zero

cell 1 needs the function added to the cell: which contains "5" already. can this be done?? or an alternative

just cell 1 to change to zero if cell 2 contains a number. cell 1 contains data already, but this has to change when cell 2 has data added to it.

i'm sure it's simple but, just cant locate the right cell for this problem

seb

rizvisa1 · Answer

Either I am not following you or you did not follow me,

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 IF(OR(L3>1,),0,K3)

Return zero in cell that contains a function

3 responses

Similar discussions

Subscribe To Our Newsletter!