Skip navigation

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

306 Views 3 Replies Latest reply: Jun 11, 2013 7:58 PM by Barry RSS
JayhawkCWE Calculating status...
Currently Being Moderated
Jun 11, 2013 5:47 PM

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.

  • Yellowbox Level 4 Level 4 (3,900 points)

    Hi Jayhawk,

     

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

     

    Screen Shot 2013-06-12 at 11.02.45 AM.png

     

    Screen Shot 2013-06-12 at 11.03.06 AM.png

     

    Formula in D2 is

    =IF(E2=TRUE,0,C2)

     

    Regards,

    Ian.

  • Wayne Contello Level 6 Level 6 (12,640 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.

     

    Using your example:

    Screen Shot 2013-06-11 at 9.47.04 PM.png

    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)

  • Barry Level 7 Level 7 (29,095 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

    Screen Shot 2013-06-11 at 7.55.41 PM.png

     

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.