Currently Being ModeratedJun 11, 2013 6:08 PM (in response to JayhawkCWE)
Currently Being ModeratedJun 11, 2013 7:48 PM (in response to JayhawkCWE)
In Numbers a cell cannot refer to itself as that is a circular reference. There are things you can do to avoid circular refs.
Using your example:
C2=B2*0.81*IF(D2, 1, 0)
this performs the original math you provided and make it conditional on the checkbox. If the checkbox is checked then B2*0.81 is multiplied by 1 otherwise by 0.
Another way would be:
C2=IF(D2, B2*0.81, 0)
Currently Being ModeratedJun 11, 2013 7:58 PM (in response to JayhawkCWE)
Further to Ian's post...
Cells in a spreadsheet can contain entered values or values that have been calculated by a formula in the cell.If you enter a value into a cell containing a formula, the entered value replaced the formula (permanently).
Ian has shown you a way to make the result (in D2) zero when the box in E2 is checked. You can move that calculation into C2 by combining the formulas in D2 and C2:
Where =B2*$C$1 was the original formula in C2.
See Example 1 below.
Or you can enter the starting value in a separate cell (A2 for Example 2 below) and use the IF function in a formula in B2 to set that value to either A2 or zero, depending on the state of the checkbox in E2:
The $C$1 cell reference pulls the percentage from cell C1 and uses that as the multiplier. The $ operators make that an absolute reference. If the formula is filled into other cells, the reference stays fixed on cell C1.