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

Feb 3, 2010 at 04:18 AM
rizvisa1
rizvisa1
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Related:
 Which function returns true if cell a1
 Which function returns true if cell a1 contains a text value  Best answers
 Which function returns true if cell a1 contains a text value ✓  Forum  Excel
 Excel if range of cell contain a text put a cell value in ✓  Forum  Excel
 If cell = date return yes or no. ✓  Forum  Office Software
 If a cell contains any text then add a value ✓  Forum  Excel
 Use the ifs function to return 5 if cell a5 contains the value 3, and return a 6 if it contains any other number. ✓  Forum  Excel
3 replies
rizvisa1
Feb 3, 2010 at 06:09 AM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
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
Feb 3, 2010 at 05:27 AM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
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