Is it possible to leave a field alone in an IF THEN formula?

Question

JayhawkCWE Author

Level 1

0 points

Is it possible to leave a field alone in an IF THEN formula?

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....

Thanks in advance.

Posted on Jun 11, 2013 5:47 PM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,383 points

Jun 11, 2013 6:08 PM in response to JayhawkCWE

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.

Reply

Answer 2

Jun 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)

Reply

Answer 3

Barry

Level 9

59,861 points

Jun 11, 2013 7:58 PM in response to JayhawkCWE

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

Reply