Return zero in cell that contains a function
Solved/Closed
seb

Feb 3, 2010 at 04:18 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Feb 3, 2010 at 06:09 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Feb 3, 2010 at 06:09 AM
Related:
 Use the ifs function to return 5 if cell a5 contains the value 3, and return a 6 if it contains any other number.
 If cell contains (multiple text criteria) then return (corresponding text criteria) ✓  Excel Forum
 If cell contains date then return value ✓  Office Software Forum
 Excel if range of cell contain a text put a cell value in ✓  Excel Forum
 Psiphon 3 download  Download  VPN
 Count if cell contains number  Excel Forum
3 replies
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
Feb 3, 2010 at 06:09 AM
Feb 3, 2010 at 06:09 AM
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)
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)
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
767
Feb 3, 2010 at 05:27 AM
Feb 3, 2010 at 05:27 AM
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
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
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 1150 imputted
Example
Forecast cost: cell 1 = 5
Actual cost: cell 2 = 1150
If cell 2 has data imputted with 1150, 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
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 1150 imputted
Example
Forecast cost: cell 1 = 5
Actual cost: cell 2 = 1150
If cell 2 has data imputted with 1150, 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