3 Replies Latest reply: Jun 11, 2013 7:58 PM by Barry
Level 1 (0 points)

I'm not sure if I'm asking this right, but I will try as hard as I can to explain it concisely.

B2 = \$180.00

C2 = B2*.81

D2 = Checkbox

How do I make an IF THEN telling B2=\$0.00 when D2=TRUE without it affecting the value in B2 if D2=FALSE?

Basically, I need the checkbox to insert a specific value (\$0.00) into B2 without affecting a value I previously entered into B2 if the checkbox is unclicked...

Is that even possible?  I can get it to zero with no problem, I just can't figure out how to make it so that I can enter a value in a cell with a formula already existing and have it not affected by the IF THEN statement if the checkbox is false... this may be a pipe dream....

• Level 6 (8,520 points)

Hi Jayhawk,

Another column to hold the formula and calculate the result, leaving original data intact. The formula is in column D:

Formula in D2 is

=IF(E2=TRUE,0,C2)

Regards,

Ian.

• Level 6 (16,935 points)

In Numbers a cell cannot refer to itself as that is a circular reference.  There are things you can do to avoid circular refs.

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)

• Level 7 (29,610 points)

Hi JayHawk,

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:

C2: =IF(E2,0,B2*\$C\$1)

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:

B2: =IF(E2,0,A2)

C2: =B2*0,81

OR

C2: =B2*\$C\$1

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.

Regards,

Barry