Return zero in cell that contains a function [Solved/Closed]

- - Latest reply: rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Feb 3, 2010 at 06:09 AM
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
See more 

3 replies

Best answer
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
754
1
Thank you
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)

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 5669 users have said thank you to us this month

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
754
0
Thank you
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
0
Thank you
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