Kate has attended:
Excel Advanced course
Changing a Cell to Zero
Hi,
Is there any way of making a cell to change to the value 0, if the formulae within it solves to a negative value?
Thanks
RE: Changing a Cell to Zero
Hi Kate
Thanks for your question.
This can be solved by using the IF function as part of your formula.
1. Start by putting in your formula (e.g. B3*C3).
2. Extend your formula to include the IF function. For example:
=IF(B3*C3<0,0,B3*C3)
What this tells Excel is that if the result of the formula is less than 0, then display 0 in the cell, otherwise display the result of the formula.
Hope this helps - Amanda
RE: Changing a Cell to Zero
Hi Kate,
Using an IF function will allow you to achieve your objective.
The IF function structure is =IF(TEST, IF TRUE, IF FALSE)
Example
A. B. C.
1. 10 20 =IF(A1*B1>0, A1*B1, "0")
2. -10 20 =IF(A1*B1>0, A1*B1, "0")
Where 1, 2 represent excel row numbers and A, B, C represent excel columns
The IF calculation in cell C1 and C2 perform a test, A1*B1>0, if that test is true then it displays the result of the calculation A1*B1, however if the test is false it displays a zero value
Hope this helps.
